Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

Firstly in your first formula you have 8 levels of Ifs, I was under the
impression that 6 was max. Use Array Formula instead using ctrl-alt-ent for
more
"trexcel" wrote in message
...
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

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

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.