IF formula help needed
I am getting #UNPARSEABLE from the formula below.
=IF([Risk Score]@row > 200, ".40", IF(AND([Risk Score]@row > 149.9, [Risk Score]@row <= 200), "0.35", IF(AND([Risk Score]@row < 150, [Risk Score]@row > 99.9), "0.2", IF(AND([Risk Score]@row < 100, [Risk Score]@row > 49.9), "0.03", IF([Risk Score]@row < 50, ".02"))))))))
I am trying to determine/ return the weighting factor of the risk score is. Below is the table. Not sure what is wrong in the formula above?
Best Answers
-
Give this one a try...
=IF([Risk Score]@row <> "", IF([Risk Score]@row > 200, ".40", IF([Risk Score]@row > 149.9, "0.35", IF([Risk Score]@row > 99.9, "0.2", IF([Risk Score]@row > 49.9, "0.03", ".02")))))
-
Take the above formula and remove the quotes from around each of the numbers.
Answers
-
You have too many parenthesis a the end of the formula. There should only be 5.
You can also shorten/simplify this formula a bit by leveraging the fact that nested IF statements read from left to right and stop on the first true value. This means that if it makes it to the second IF, then the first MUST be false.
Another way to say that is that if your formula makes it past the first IF because the number is not greater than 200, you do not need to specify "less than 200" in the second IF because it has to be by default for it to even get that far.
=IF([Risk Score]@row > 200, ".40", IF([Risk Score]@row > 149.9, "0.35", IF([Risk Score]@row > 99.9, "0.2", IF([Risk Score]@row > 49.9, "0.03", ".02"))))
-
Thank you, that works. @Paul Newcome one question though if none of the values are present and I want it to return a blank or leave the cell blank what would I add?
-
Give this one a try...
=IF([Risk Score]@row <> "", IF([Risk Score]@row > 200, ".40", IF([Risk Score]@row > 149.9, "0.35", IF([Risk Score]@row > 99.9, "0.2", IF([Risk Score]@row > 49.9, "0.03", ".02")))))
-
@Paul Newcome thank you for your help. Now I have another issue that I can't see to crack.
Now that I have the weight of the risk score calculated I am trying to get to the weighted value by multiplying risk score by risk weight factor =[Risk Score]@row * [Risk weight factor]@row and it gives me an invalid operation. What am I missing?
-
Take the above formula and remove the quotes from around each of the numbers.
-
@Paul Newcome Thank you! I would not have figured that out. Thanks so much
-
Happy to help. 👍️
Quotes around anything indicate a text string. To get actual numeric values to output, you need to remove the quotes and just leave the numbers.
-
@Paul Newcome I have another layer to this formula that I need to figure out.
=IF([Risk Score]@row <> "", IF([Risk Score]@row > 200, 0.4, IF([Risk Score]@row > 149.9, 0.35, IF([Risk Score]@row > 99.9, 0.2, IF([Risk Score]@row > 49.9, 0.03, 0.02)))))
In the 3 scenarios below, the result in the Risk weight factor column should return 0. How do I adjust the current formula to return 0 if there isn't a probability or impact score?
-
Try this...
=IF(OR([Probability Score]@row = 0, [Impact Score]@row = 0), 0, IF([Risk Score]@row <> "", IF([Risk Score]@row > 200, 0.4, IF([Risk Score]@row > 149.9, 0.35, IF([Risk Score]@row > 99.9, 0.2, IF([Risk Score]@row > 49.9, 0.03, 0.02))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!