Jump to content
Sign in to follow this  

If number set meets criteria then calculate

Recommended Posts

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



Share this post

Link to post
Share on other sites

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 by Steve Martino

Share this post

Link to post
Share on other sites
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.

  • Like 1

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  


Important Information

By using this site, you agree to our Terms of Use.