The best way to do this would be by setting up an Excel Spreadsheet or an Access Database then use the External Links in Monarch. That way you can bring in as many columns as you need, if you use a Lookup table in Monarch you only get one column. Then again you could have 2 Lookup Tables.
Hope that gives you enough to go on.
As a rule I would tend to use an internal lookup table for something that was not going to change or not change very often (Month number to Month name for example) but seriously consider an external lookup for something that might change regularly - an exchange rate for example.
One thing to consider strategically for Monarch use within an organisation is who maintains any external tables. They may of course be a by-product of the whole process from another Monarch model but they may also be produced elsewhere.
Sometimes the person responsible for the lookup table may be happier simply producing a text file so it's worth keeping in mind that a simple text
file can also be used as a viable delimited text lookup 'database'.
Sounds like you're having fun and getting up to speed quite rapidly.
I do have another question and hope you can help:
Is there a limit to the number of IF statements within a cell? Also, have you come across where the if.and.or needs to be in a certain order?
I have this formula:
The problem is where I have a FLOOR_BRO equal to MANFIN, however a COMMODITY of "EUBOL3M/FUT" This combination does not meet the criteria, so it should show zero, however it shows a calculated number?So I am wondering if my formula is correct?
The problem is where I have a FLOOR_BRO equal to MANFIN, however a COMMODITY of "EUBOL3M/FUT" This combination does not meet the criteria, so it should show zero, however it shows a calculated number? Thanks
I try to avoid the and/or combinations - mostly I am lucky in not dealing with anything that needs them!
I think you need to group the the OR values in brackets for the correct order of processing but with V8 I would tend to use the .IN. operator I think.
The Help offers more examples.
If that's not it either I am on completely the wrong track or there is something else coming into play in your data. We could toss a coin on which it is if you like
Thanks Grant, What I did was break it down into more IF statements than I wanted, but it works fine.
Something like this:
I will try the IN formula as well.
Is there a limit to the number of IF's in a formula?
What you are seeing the is the order of operations in mathematics. Equations are always calulated from left to right, brackets 1st, ANDs and ORs next, then powers (exponents), then multiplations and divisions, then finally addition & subtraction. Plus a few other tidbits I don't recall from high school, but that's the gist of it.
IF(COMMODITY="EURO/FUT" .And. FLOOR_BRO="MANFIN" .Or. FLOOR_BRO="LEHBROS",Eq_Trader2*,0)[/quote]would mean that commodity = "Euro" is evulated 1st. This gives a true or false, then the AND floor = "manfin" is evalulated and if BOTH are true then it goes on.
Anyhow Grant's idea is fantastic or you can always use brackets to change the equation order.
EG IF(COMMODITY="EURO/FUT" .And. (FLOOR_BRO="MANFIN" .Or. FLOOR_BRO="LEHBROS"),Eq_Trader2*,0) /quotewould force the OR statement to be done before going back to the AND statment.
PS most other softwares will work this way, not just Monarch