help with a formula question
Hi,
I'm trying to use this formula to evaluate whether a value should appear Red, Green or Yellow by multiplying to other values in the sheet, but I keep getting an #unparsable error.
=IF(sum([Severity]1*[Probability]1) >"1.0", "Red", IF(AND(sum[Severity]1*[Probability]1) >".50", (sum[Severity]1*[Probability]1) <".75"), "Yellow", "Green"))
I'm not sure why - if someone else has run into this issue, please let me know how to resolve it.
thanks,
Michelle
Best Answers
-
You have a misplaced parenthesis. Move the opening parenthesis from before the 3rd "sum" to immediately after it.
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!
-
Knowing what you want it to do, try this one instead...
=IF(Severity@row * Probability@row > .75, "Red", IF(Severity@row * Probability@row > .5, "Yellow", "Green"))
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!
-
Happy to help. 👍️
With nested IFs, the formula will stop on the first true value. Using that logic, if it moves past an IF then that particular IF must already be false. So by the time we get to the "Yellow" output, we already know that it must be less than .75 so there is no real need to repeat that as a criteria.
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!
Answers
-
You have a misplaced parenthesis. Move the opening parenthesis from before the 3rd "sum" to immediately after it.
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!
-
Thank you I'll try that.
-
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!
-
Hi,
I had to set this formula aside for other priorities but it's still not working properly.
I moved the parentheses but it's still not working. Just to refresh my original question was around evaluating the sum of Severity*Probability to determine my risk score and put a graphical indicator on it. If the value is less than .50 it's green, if it's greater than .50 but less than .75 it's yellow and greater than .75 is red.
=IF(sum([Severity]@row*[Probability]@row) >"1.0", "Red", IF(AND(sum[Severity]@row*[Probability]@row) >".50", sum[Severity]@row*[Probability]@row) <".75"), "Yellow", "Green"))
Any suggestions?
Thanks
Michelle
-
Knowing what you want it to do, try this one instead...
=IF(Severity@row * Probability@row > .75, "Red", IF(Severity@row * Probability@row > .5, "Yellow", "Green"))
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!
-
That worked! Thank you for your help Paul. I was struggling with that one! I thought yellow wasn't going to work unless I defined a range between .5 and .75. I think I was overthinking it. Thanks again
-
Happy to help. 👍️
With nested IFs, the formula will stop on the first true value. Using that logic, if it moves past an IF then that particular IF must already be false. So by the time we get to the "Yellow" output, we already know that it must be less than .75 so there is no real need to repeat that as a criteria.
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!
-
Thanks, I'll think about that when working on future nested ifs.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives