IF statements – multiple levels

IF statements really bring power to your spreadsheets. And multiple IFs, even more so.

Before 2007, the limit was 7 nested IFs in one statement. My advice was to avoid anything more than 3, because it would make your head spin.

Multiple levels are difficult to follow, meaning it is easy to make mistakes, and a real problem trying to debug them.

It is also very easy to put a comma in the wrong place and obtain a completely different result from that which was intended.

This combined with the issue that logic doesn’t always behave as expected (particularly where negatives are involved) increases the potential for error.

And, even if you get it right, it can make future changes and development tricky for the same reasons.

And from 2007 on, the limit has increased to 64!

As a simple rule of thumb, if you find yourself going beyond 3 levels, then there’s probably a better way of doing it (eg Vlookup, Index).

Leave a Reply

Your email address will not be published. Required fields are marked *