If number set meets criteria then calculate

[wattmhite]

I'm looking to create a calculation field that takes the field 6 Total Premium and if that field is between 0 and 2,501 then calculates field Financed Monthly Amount by 1.06032941. If 6 Total Premium = 2,501 to 5,000 then calculate by a different amount, etc etc.

What is the best way to accomplish this, I started setting up the calc below but it will not work.

Thank you so much!

 

If ((${6 Total Premium} > 0) and (${6 Total Premium} < 2501)) ; Financed Monthly Amount * 1.06032941

 

 

[Steve Martino]

Case (6 Total Premium <2502; Financed Monthly Amount * 1.06032941;

          6 Total Premium <5001; Financed Monthly Amount * <different amount>;

          --as many scenarios as you need or default amount--

)

 

Edited March 31, 2017 by Steve Martino

[comment]

3 hours ago, wattmhite said:

What is the best way to accomplish this

First thing you should do is rename the 6 Total Premium field to a valid name.

The next step depends on whether the brackets and the associated rates are constant and will never change, or can they be updated at some point (as is often the case with inflation and fluctuating markets).

If they are constant, then use the Case function along the lines shown by Steve Martino (except you could reduce it to =

Financed Monthly Amount * Case (
Total Premium < 2501 ; 1.06032941 ;
Total Premium < 5000 ; 1.05 ; 
Total Premium < 7500 ; 1.04 ; 
// more brackets here 
1.03 )

). Otherwise you will need to lookup the rate from a table of current rates into a local field, where it will be protected from future changes.