Math Logic Formula - IF THEN ELSE? Part II

Rachelle K

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

Rachelle K ✭

08/14/24

Yes! Thank you so much.

Did this answer the question? Yes No

Paul Newcome

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)))

DavidMMM

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)))

Rachelle K

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 drop-down option of "mulitplier" and free form number "survey".

Thanks so much for the help!
Rachelle

Paul Newcome

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)))

Rachelle K

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

Paul Newcome

Happy to help. 👍️