What is an alternative to multiple-nested IF-Then-Else functions?

IF-Then-Else - one of the functions that Excel has taken from the programming languages. And it is one of those things that one learns as basics of programming.


If-Then-Else (or simply IF function) is used to make a choice based on condition(s) provided by the user. For example:


In one of the cells, I want to show "True" IF cell A1 contains 1, otherwise it should show "False".


To achieve the above, one can write a simple formula in Excel, i.e. - =If (A1=1,"TRUE","FALSE")


Great! Now let us change the above task a bit, something as - the cell B1 should show the following text based on the number input:

• If cell A1 = 1, then the cell should read "One"
• If cell A1 = 2, then the cell should read "Two"
• If cell A1 = 3, then the cell should read "Three"
• If cell A1 = 4, then the cell should read "Four"
• Otherwise should say - "Above 4"

In this case, one can write a multiple IF formula as this:

= IF (A1=1,"One", IF(A1=2,"Two", IF(A1=3,"Three", IF(A1=4,"Four","Above 4") ) ) )

Cool! It works. Now let us make things more complex. Lets keep 7 conditions instead of 4. You can still write the IF function on above lines and it will work fine.

But what if there are more than 7 conditions? And what about the processing time?

Excel does provide you with a useful tool such as IF function, but it comes with a limitation. The limitation is that a cell in Excel can contain only 7 nested IF functions, not more than that.

The other issue with using multiple IF functions is that it makes processing a bit slow, as there are 7 IF functions to deal with. You will not be able to notice the speed if you have only one cell with multiple IF functions, but in case you had a sheet with many such cells, the calculations will take time.

The alternative to IF functions is to use Choose function. This functions allows you to provide several options within the function, which will be processed on the basis of number provided to it as input. I have provided more text on this function in a separate post.

Share on Facebook Share on Twitter Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.