8 levels or more IF function for EXCEL 2003 or below

"the specified formula cannot be entered because it uses more levels than are allowed in the current file format"

For those who actively use Microsoft Excel, may had found the error statement above. It caused by the limitation of nesting level of older version of the application (2003 and below). They only support up to 7 nesting level of a function in one statement.

There are some alternative ways to get around, here's some of them:

1. Upgrade your EXCEL to 2005 or above.
2. If you had a macro programming skill, create a function in VBA.
3. or, you can split the function up to some parts.

Here, we'll try the last option, Split up the function.

I have this function that had the nesting level issue:

=IF(H6<91.5%,40%,if(h6<92.5%,35%,if(h6<93.5%,30%,if(h6<94.5%,25%,if(h6<95.5,20%,if(h6<96.5%,16%,if(h6<97.5%,12%,if(h6<98.5%,8%,if(H6<99.5%,4%,0%)))))))))

as the limitation goes, than the function will only work if we cut the red part off:

...if(H6<99.5%,4%,0%)...

But that won't solve the problem. So, I came up with this way.

• Take the 8th level of nesting out (let's say use the C2 cell) and exchange it with "FALSE" value, so the function will look like this:

=IF(H6<91.5%,40%,if(h6<92.5%,35%,if(h6<93.5%,30%,if(h6<94.5%,25%,if(h6<95.5,20%,if(h6<96.5%,16%,if(h6<97.5%,12%,if(h6<98.5%,8%,FALSE))))))))

• Put the 8th IF to another cell and put another IF statement before it to check the output value of the IF statement above (from the C2 cell). If it return FALSE then execute the 8th IF. So the second part of the function will be like this:

=IF($C2=FALSE,IF(H6<99.5%,4%,0%))

• The last part is to choose which output to use. Here we'll use another IF. Check whether the return value of the first statement is FALSE or not. If it is, then use the return value of the second if statement. If not, then just use the value. Here's what it would be like:

=IF($C2=FALSE,$D2,$C2)

There you go! We finally made it. Hope you'll find this article useful. If you want to see the real example of the EXCEL format, I have uploaded it here. Have a good day!