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.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
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!