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

Find more posts tagged with

Accepted answers

Protonsponge

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

All comments

Protonsponge

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

Yes! Thank you so much.

Rachelle K

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.