Solved

Calculated Value field based on dollar amount

  • 1 March 2022
  • 1 reply
  • 2 views

Badge +3

I have a "Invoice Total" field that the user inputs a dollar value.  I want to set a Calculated Value field based on the dollar value like this:

























































Invoice Total Field Calculated Value Field
$0 up to $50,000 Stage1
$50,001 to $250,000 Stage2
$250,001 to $500,000 Stage3
$500,001 to $1,999,999 Stage4
$2,000,000 and over Stage5


For Example, if the user inputs a Invoice Total of $73,000, I'd want the calculated value field to display to "Stage2" etc.



 



What would be the best way to achieve this in the form?





icon

Best answer by Aleximo 2 March 2022, 11:35

View original

1 reply

hey @jambou ,



 



u could use a nested if statement with an and function.



 



the formula should look like:



 



IF(Invoice Total <=50000, "Stage1", IF(AND(Invoice Total >=50001, Invoice Total <=250000), "Stage2", IF(AND(Invoice Total >=250001, Invoice Total <=500000), "Stage3", IF(AND(Invoice Total >=500001, Invoice Total <=1999999), "Stage4", IF(Invoice Total >=2000000, "Stage5", "")))))

Reply