What is wrong with this formula?
 

Hi,

Am i entering too many arguements or is there some other issue?


=IF(I300,"",IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT
APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT
APPLICABLE",I28=0),OR(I27="NOT
APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I84,"N")-1)*0.03)),IF(AND(I27=1,I280),((I12/(1-I11))-(0.5)-((COUNTIF(I75:I84,"N"))*0.03))))))))))

What is wrong with this formula?
 

Gary

You can only have 7 nested IFs (Up to 2007). This is normally handled either
by named ranges or a table and then a VLOOKUP function

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"Gary" wrote in message
...
Hi,

Am i entering too many arguements or is there some other issue?


=IF(I300,"",IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT
APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT
APPLICABLE",I28=0),OR(I27="NOT
APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I84,"N")-1)*0.03)),IF(AND(I27=1,I280),((I12/(1-I11))-(0.5)-((COUNTIF(I75:I84,"N"))*0.03))))))))))

What is wrong with this formula?
 

There is a limit of 7 nested functions in Excel before 2007. You have
9 IFs and several AND and ORs, so you have probably exceeded this
limit.

Hope this helps.

Pete

On Oct 29, 1:48 pm, "Gary" wrote:
Hi,

Am i entering too many arguements or is there some other issue?

=IF(I300,"",IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT
APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT
APPLICABLE",I28=0),OR(I27="NOT
APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(*I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I84,*"N")-1)*0.03)),IF(AND(I27=1,I280),((I12/(1-I11))-(0.5)-((COUNTIF(I75:I84,"*N"))*0.03))))))))))

What is wrong with this formula?
 

It'll be the levels of nesting. Limited to 7.
--
David Biddulph

"Gary" wrote in message
...
Hi,

Am i entering too many arguements or is there some other issue?


=IF(I300,"",IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT
APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT
APPLICABLE",I28=0),OR(I27="NOT
APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I84,"N")-1)*0.03)),IF(AND(I27=1,I280),((I12/(1-I11))-(0.5)-((COUNTIF(I75:I84,"N"))*0.03))))))))))