 SOLVED

New Contributor

# Why can't I nest more than 8 IF Functions?

Hi,

According to the Excel Help tutorial, you can nest up to 64 IF functions. But I am only permitted to nest 8. Excel returns this error message as soon as I press enter: The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format.

My formula:

=IF(G22<=10,"60",IF(G22>=11,"75",IF(G22>=21,"90",IF(G22>=31,"105",IF(G22>=41,"120",IF(G22>=51,135",IF(G22>=61,"150",IF(G22>=71,"165",IF(G22>=81,"180",IF(G22>=91,"195",IF(G22>=101,"210",IF(G22>=111,"225",IF(G22>=121,"240",IF(G22>=131,"255",IF(G22>=141,"270",IF(G22>=151,"285",IF(G22>=161,"300",IF(G22>=171,"315",IF(G22>=181,"330","")))))))

Basically, if the test is not true, there should be an incremental increase of 15 each time.

What am I doing wrong that prevents nesting beyond eight nests?

4 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Why can't I nest more than 8 IF Functions?

You have probably saved the file as an Excel 2003 file type, which will limit this feature to what Excel 2003 was capable of. But you shouldn't be using that many nested ifs. Create a small table which has the limits like so:

10 60
11 75
21 90
31 105
41 120

(truncated, you get the idea)

Suppose this table is on Sheet2!\$A\$1:\$B\$19 then use this formula:

=VLOOKUP(G22,Sheet2!\$A\$1:\$B\$19,2,TRUE)

# Re: Why can't I nest more than 8 IF Functions?

Thank you so much. I was not familiar with the VLOOKUP function but it works. Thanks!

# Re: Why can't I nest more than 8 IF Functions?

You're welcome!
You can makr my answer as "Best response" if you ike it.

# Re: Why can't I nest more than 8 IF Functions?

Yes, My problem occurred when save file to .xls format . Problem Solved. Thanks@ Muza Wever