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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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]".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!