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

About spreadsheetexpertblog

Working as a computer professional since 1983, on projects varying in size from one person to multi-million pounds. Working on Excel projects since the mid 1990s.
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s