Please can you help me getting a IF(AND Formula working

Barry.davies

Hi, I have the below calculation, it all works apart from the part in Bold, if I show a number between 13 and 20 I get a #INCORRECT ARGUMENT error. any help will be greatly recieved.

=IF(["Score Probability x Impact"]@row < 7, "Low", IF(AND(["Score Probability x Impact"]@row > 7, ["Score Probability x Impact"]@row < 13), "Moderate", IF(AND(["Score Probability x Impact"]@row > 13, ["Score Probability x Impact"]@row < 21), "High", IF(["Score Probability x Impact"]@row > 21), "Extreme")))

Many Thanks

Paul Newcome

Something that should help is that nested IFs work from left to right and stop on the first true value. This means that if it skips an argument, it is considered false as the rest of the formula is evaluated.

This means you can chain together certain IFs in a specific order to keep yourself from having to duplicate arguments.

For example…

Your first IF says the score is less than seven. If that is false and the formula moves past it, we can assume it is false in all other IF statements, and we don't need to repeat it.

This means we can simplify your formula by removing the AND statements which also removes parenthesis which tend to be a pretty common cause for that particular error…

=IF(["Score Probability x Impact"]@row < 7, "Low", IF(["Score Probability x Impact"]@row < 13, "Moderate", IF(["Score Probability x Impact"]@row < 21, "High", "Extreme")))

If that doesn't fix it… How exactly are you populating the score column?

Paul Newcome

Something that should help is that nested IFs work from left to right and stop on the first true value. This means that if it skips an argument, it is considered false as the rest of the formula is evaluated.

This means you can chain together certain IFs in a specific order to keep yourself from having to duplicate arguments.

For example…

Your first IF says the score is less than seven. If that is false and the formula moves past it, we can assume it is false in all other IF statements, and we don't need to repeat it.

This means we can simplify your formula by removing the AND statements which also removes parenthesis which tend to be a pretty common cause for that particular error…

=IF(["Score Probability x Impact"]@row < 7, "Low", IF(["Score Probability x Impact"]@row < 13, "Moderate", IF(["Score Probability x Impact"]@row < 21, "High", "Extreme")))

If that doesn't fix it… How exactly are you populating the score column?

Barry.davies

Thanks so much, I did get the way I had written it working. However your logic and approach to this far exceeds mine, it is a much better and cleaner formula which works great.

Thanks again