Multiple IF statments
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?
Answers
-
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))
-
It says unparseable.
-
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))))
-
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.
-
Yes, when I copied/pasted your formula it gave me the #unparseable error.
-
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]".
-
Ah, that would do it. I changed the column headings. Either way, it is working now. Thanks!
-
Ok. Phew. Glad I wasn't losing it. Haha.
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!