Forum Discussion
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?
- 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)
4 Replies
- Yes, My problem occurred when save file to .xls format . Problem Solved. Thanks@ Muza Wever
- 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)Thank you so much. I was not familiar with the VLOOKUP function but it works. Thanks!
- You're welcome!
You can makr my answer as "Best response" if you ike it.
