Math Logic Formula  IF THEN ELSE?
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

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

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.

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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!