IF formula help needed

Melissa Torrez

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?

Paul Newcome

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

Take the above formula and remove the quotes from around each of the numbers.

Paul Newcome

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"))))

Melissa Torrez

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?

Paul Newcome

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")))))

Melissa Torrez

@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?

Paul Newcome

Take the above formula and remove the quotes from around each of the numbers.

Melissa Torrez

@Paul Newcome Thank you! I would not have figured that out. Thanks so much

Paul Newcome

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.

Melissa Torrez

@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?

Paul Newcome

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))))))