Multiple IF statments

Joshua DeMille · 03/29/21

I have a probability column (High, Med, Low).. when updated to one of these I want it to apply a predetermined percentage to a sum of rows (projected sales).

Example, Projected Sales =Sum of columns 1-4, If High I want the sum to be multiplied by .75, if Med by .5 and if Low .25.

I can get this formula to work for one variable (High in this case), but cannot add the same parameters for Med and Low.

=IF(Probability@row = "High", SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * 0.75) .. that works fine but when I add additional IF statements in the same format it gives me an error.

=IF(Probability@row = "High", SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * 0.75), IF(Probability@row = "Medium", SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * 0.5) and I get an error.

Please advise, am I using this formula incorrectly or am I just using the wrong formula? Can Smartsheet do what I want in this case?

Paul Newcome · 03/29/21

Let's separate the two a little bit to save you some key strokes. If we do the SUM

SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row)

and then multiply by the nested if, you only need to key the SM portion once.

=SUM([Q1 2021 Sales]@row:[Q4 2021 Sales]@row) * IF(Probability@row = "High", 0.75, IF(Probability@row = "Medium", 0.5, 0.25))

Joshua DeMille · 03/29/21

It says unparseable.

Joshua DeMille · 03/29/21

https://community.smartsheet.com/discussion/comment/285384#Comment_285384

I got it to work.. needed some extra parentheses at the end.

=SUM([Q1 2021 Closings]@row:[Q4 2021 Closings]@row) * (IF(Probability@row = "High", 0.75, IF(Probability@row = "Medium", 0.5, IF(Probability@row = "Low", 0.25))))

Paul Newcome · 03/29/21

It looks like you used an extra opening parenthesis before the IF statement, and you used an additional IF statement as well which would lead to the additional closing parenthesis being needed.

Really though the set of parenthesis isolating the nested IF shouldn't have been necessary because even though it is nested it is still a single output.

And the 3rd IF statement shouldn't have been needed either since we used the final portion of the second IF to cover the 3rd output.

Were you getting an error with my formula? I am curious as to why it didn't work.

Joshua DeMille · 03/29/21

Yes, when I copied/pasted your formula it gave me the #unparseable error.

Paul Newcome · 03/29/21

Ah. It looks like there may have been a change in column names?

Your original post has "[Q1 2021 Sales]" whereas the formula you posted that you said was working for you has "[Q1 2021 Closings]".

Joshua DeMille · 03/29/21

Ah, that would do it. I changed the column headings. Either way, it is working now. Thanks!

Paul Newcome · 03/29/21

Ok. Phew. Glad I wasn't losing it. Haha.

Happy to help. 👍️

Multiple IF statments

Answers

Help Article Resources

Categories