Math Logic Formula  IF THEN ELSE? Part II
I have an update to my original question below.
I now want to take this formula =IF([Survey Calc]@row < 121000, 121000, IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row)) and change it a bit because I realize I forgot we have an additional option.
I need to add a zero option: I need the formula to have an option if {Survey Calc} is 0 (zero) then return 0 (zero), if >0, <121000 return 121000, if . 300000 return 300000, else return [Survey Calc}.
I'm stuck on the if >1<121000 part. Thinking it's something like this, but this is still my first template and trying to do this from reading past questions:
=([Survey Calc]@row = 0, 0, (IF([Survey Calc]@row > 1 < 121000, 121000), IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row)))
Any assistance is appreciated!!
Rachelle
Original POST:
08/14/24
Hoping someone can help with what I think is an IF, THEN, ELSE logic math calculation. I created an IF, THEN formula but now need to step it up. Need to create a formula that will calculate an amount due based upon a previous formula but there is a minimum and maximum ceiling to the amount that can be due.
We enter an amount in the “Survey Amount” column and multiply it by “Multiplier” and come up with “Survey Calc”. The trick is that the maximum “Survey Calc” is 3,000,000 and the minimum is 121,000. Anything is between is whatever the calculation returns.
For the maximum I had: =IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row) and it worked. I’ve looked at Community answers for help and am sure I’m just missing a () here or there. Any help would be greatly appreciated as I’m new to Smartsheets. Thanks!!
Best Answer
Protonsponge ✭✭✭✭✭✭
08/14/24 edited 08/14/24 Answer ✓
Would this work for you?
=IF([Survey Calc]@row < 121000, 121000, IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row))
I hope that if helpful to you in some way,
Protonsponge
Answers
Protonsponge ✭✭✭✭✭✭
08/14/24 edited 08/14/24 Answer ✓
Would this work for you?
=IF([Survey Calc]@row < 121000, 121000, IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row))
I hope that if helpful to you in some way,
Protonsponge
Yes! Thank you so much.
Best Answer

Looks like you have used too many parenthesis. Give this a try:
=IF([Survey Calc]@row = 0, 0, IF([Survey Calc]@row < 121000, 121000, IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row)))
Answers

I get what you're trying to do next. Smartsheet does not directly allow you to do a "greater than but less than" function as you're thinking about it. As long as you include the 0 call as the first part of your formula (the most restrictive bound), that will activate first and stop, regardless of if your next part of your formula calls for <12000.
In your formula, remove the " > 1" piece, and you should be good to go. You just need a leading IF at the very front to the right of the =.
=([Survey Calc]@row = 0, 0, (IF([Survey Calc]@row
> 1< 121000, 121000), IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row))) 
This didn't work, said it's unparseable. Following what you said, I added IF at the beginning:
=IF([Survey Calc]@row = 0, 0, (IF([Survey Calc]@row < 121000, 121000), IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row)))
I also removed the IF. I also added some parenthesis at the end and removed some at the end but get getting the syntax error.
Trying to get $0.00 if survey is 0; 121,000 is survey is calc is less than 121,000; 300,000 if survey calc is over 300,000 and the actual value of survey calc if between 121,000 and 300,000.
"Survey calc" is a calculation of two other columns; a dropdown option of "mulitplier" and free form number "survey".
Thanks so much for the help!
Rachelle 
Looks like you have used too many parenthesis. Give this a try:
=IF([Survey Calc]@row = 0, 0, IF([Survey Calc]@row < 121000, 121000, IF([Survey Calc]@row > 3000000, 3000000, [Survey Calc]@row)))

That did the trick, Paul. Thanks so much. I'll be watching some videos now that I have this immediate need addressed.
Thanks to the Community for the assist!
Rachelle 
Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!