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!