Calculated Fields

[Gareth Horton]

Colleen McKay wrote:
> I'm a relatively new user of Monarch so I'm full of questions.
> My first is:
> I am trying to use a calculated field to assign a more literal term to a field so that I can make
> the report more reader friendly.
>
> For example: If Insurance Class=01 then print "State Funded"
> I can get this to work but my problem is that I have 20 insurance classes to assign different terms to.
> Nested functions don't seem to be the way to go with that many comparisons.
>
> Any suggestions?

What version of Monarch do you have? If you have V6, you can setup a lookup table which would be really easy. If you have a prior version, it would be as follows:
if{[Insurance Class]="01","State Funded",if{[Insurance Class]="02","City Funded","County Funded"))
Just make sure your beginning and ending parentheses match up.

I have attached an example of nested IF statment that calculates a time zone label according to a designated state code. It isn't pretty but it works. Don't forget to have the correct number of closing brackets, ), at the end. This is usually the cause for an error in the calculation. The error would read "Invalid delimeter" if you don't have enough closing brackets.

I used a state list in Excel to build the calculation by using the concatenate function and copying the formula down. If you would like more on this method of building a model please let me know.

Thanks,
Scott

Colleen McKay wrote:
> I'm a relatively new user of Monarch so I'm full of questions.
> My first is:
> I am trying to use a calculated field to assign a more literal term to a field so that I can make
> the report more reader friendly.
>
> For example: If Insurance Class=01 then print "State Funded"
> I can get this to work but my problem is that I have 20 insurance classes to assign different terms to.
> Nested functions don't seem to be the way to go with that many comparisons.
>
> Any suggestions?

/*This Model assigns a time zone label by state*/

if(State="AL","Central",if(State="AK","Alaskan",if(State="AZ","Mountain",if(State="AR","Central",if(State="CA","Pacific",if(State="CO","Mountain",if(State="CT","Eastern",if(State=" DE","Eastern",if(State="DC","Eastern",if(State="FL","Central/Eastern",if(State="GA","Eastern",if(State="HI","Hawaiian",if(State="ID","Mountain",if(State="IL","Central",if(State="IN ","Eastern",if(State="IA","Central",if(State="KS","Central",if(State="KY","Central/Eastern",if(State="LA","Central",if(State="ME","Eastern",if(State="MD","Eastern",if(State="MA","E astern",if(State="MI","Eastern",if(State="MN","Central",if(State="MS","Central",if(State="MO","Central",if(State="MT","Mountain",if(State="NE","Mountain/Central",if(State="NV","Pac ific",if(State="NH","Eastern",if(State="NJ","Eastern",if(State="NM","Mountain",if(State="NY","Eastern",if(State="NC","Eastern",if(State="ND","Mountain/Central",if(State="OH","Easte rn",if(State="OK","Central",if(State="OR","Pacific",if(State="PA","Eastern",if(State="PR","Eastern",if(State="RI","Eastern",if(State="SC","Eastern",if(State="SD","Mountain/Central" ,if(State="TN","Central/Eastern",if(State="TX","Central",if(State="UT","Mountain",if(State="VA","Eastern",if(State="VI","Eastern",if(State="VT","Eastern",if(State="WA","Pacific",if (State="WI","Central",if(State="WV","Eastern",if(State="WY","Mountain","None Found")))))))))))))))))))))))))))))))))))))))))))))))))) )))