maximum levels of nesting if

**mkb_cma** · 10-24-2011, 02:52 AM

Hi,

I am receiving the below message while trying a formula.

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

Formula is:

=IF(OR(C9=C8,C9=C7),0,IF(B9=1,MAFRAQ!K$68,IF(B9=2,MAFRAQ!L$68,IF(B9=3,MAFRAQ!M$68, IF(B9=4,MAFRAQ!N$68, IF(B9=5,MAFRAQ!O$68, IF(B9=6,MAFRAQ!P$68, IF(B9=7,MAFRAQ!Q$68, IF(B9=8,MAFRAQ!R$68, IF(B9=9,MAFRAQ!S$68, IF(B9=10,MAFRAQ!T$68, IF(B9=11,MAFRAQ!U$68, IF(B9=12,MAFRAQ!V$68, IF(B9=13,MAFRAQ!W$68, IF(B9=14,MAFRAQ!X$68, IF(B9=15,MAFRAQ!Y$68, IF(B9=16,MAFRAQ!Z$68, IF(B9=17,MAFRAQ!AA$68, IF(B9=18,MAFRAQ!AB$68, IF(B9=19,MAFRAQ!AC$68, IF(B9=20,MAFRAQ!AD$68, IF(B9=21,MAFRAQ!AE$68, IF(B9=22,MAFRAQ!AF$68, IF(B9=23,MAFRAQ!AG$68, IF(B9=24,MAFRAQ!AH$68, IF(B9=25,MAFRAQ!AI$68, IF(B9=26,MAFRAQ!AJ$68, IF(B9=27,MAFRAQ!AK$68, IF(B9=28,MAFRAQ!AL$68, IF(B9=29,MAFRAQ!AM$68, IF(B9=30,MAFRAQ!AN$68, IF(B9=31,MAFRAQ!AO$68,0))))))))))))))))))))))))))))))))

Please help.

Regards,

**zbor** · 10-24-2011, 02:57 AM

Here, try this:

=IF(COUNTIF(C7:C8, C9)>0, 0, OFFSET(MAFRAQ!$J$68, 0, B9))

**zbor** · 10-24-2011, 03:08 AM

But I didn't give you answer...

In Excel 2007 (as your profile say), a formula can contain up to 64 levels of nesting, but in earlier versions of Excel, the maximum levels of nesting is only 7.

So it should be fine... But.. If you using more than 5 IF's... You are probably on the wrong way, and you need some other approach.

Some formulas have more levels of nesting than are supported by the selected file format. Formulas with more than seven levels of nesting will not be saved and will be converted to #VALUE! errors

In the Compatibility Checker, click Find to locate the cells that contain formulas with more than 7 levels of nesting, and then make the necessary changes to avoid #VALUE! errors.

**martindwilson** · 10-24-2011, 03:15 AM

or try this one
=IF(OR(C9=C8,C9=C7),0,INDEX(mafraq!K68:AN68,B9))

**zbor** · 10-24-2011, 03:20 AM

Yes, INDEX approach is better.

**mkb_cma** · 10-24-2011, 03:59 AM

Thanks Martindwilson and zbor,

It worked.

=IF(OR(C9=C8,C9=C7),0,INDEX(mafraq!K68:AN68,B9))

Best Regards,

**Mordred** · 02-11-2012, 03:04 AM

If your problem has been solved then please mark your thread as such.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save