Please can you help me getting a IF(AND Formula working
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
Best Answer
-
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?
Answers
-
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? -
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!