Error in Formula ??

[Corey]

I am getting an error in this formula:

=IF(AND(C5<>"Non U/G",C5<>"Delta",C7>V2,C7<W2,C8>W2),"0.75",IF(AND(C5="Delta",C7>V2,C7<W2,C8>W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delta",C7<V2,C8<V2),"1",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>=W2),"0.75",IF(AND(C5="Delta",C7<V2,C8>=W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>V2,C8<=W2),"0.75",IF(AND(C7<V2,C8>V2,C8<=W2,"0.5","0"))))))))

Can anyone tell me why?

If i change it by dropping off the last bit(but is needed) the error is not present:

=IF(AND(C5<>"Non U/G",C5<>"Delta",C7>V2,C7<W2,C8>W2),"0.75",IF(AND(C5="Delta",C7>V2,C7<W2,C8>W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delta",C7<V2,C8<V2),"1",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>=W2),"0.75",IF(AND(C5="Delta",C7<V2,C8>=W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>V2,C8<=W2),"0.75","0")))))))


Is it becase it has reached the limit of the Formula?
Is there a way to simplify the above?


Regards

Corey

 

[Corey]

=IF(AND(C5<>"Non U/G",C5<>"Delta",C7>V2,C7<W2,C8>W2),"0.75",IF(AND(C5="Delta",C7>V2,C7<W2,C8>W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delta",C7<V2,C8<V2),"1",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>=W2),"0.75",IF(AND(C5="Delta",C7<V2,C8>=W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>V2,C8<=W2),"0.75",IF(AND(C7<V2,C8>V2,C8<=W2),"0.5","0"))))))))

Sory the actual code with the error is above, I missed a Bracket.
Error is still present though.

 

[Guest]

You have one too many nesting levels at eight and also, you either have an
unintended comma or are missing a condition in the following And function:

IF(AND(,C5="Delta",C7<V2,C8<V2)…

Note that this will always return False as currently written:

Regards,
Greg

 

[Ron Rosenfeld]

=IF(AND(C5<>"Non U/G",C5<>"Delta",C7>V2,C7<W2,C8>W2),"0.75",IF(AND(C5="Delta",C7>V2,C7<W2,C8>W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delta",C7<V2,C8<V2),"1",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>=W2),"0.75",IF(AND(C5="Delta",C7<V2,C8>=W2),"0.5",IF(AND(C5<>"Non U/G",C5<>"Delta",C7<V2,C8>V2,C8<=W2),"0.75",IF(AND(C7<V2,C8>V2,C8<=W2),"0.5","0"))))))))

Sory the actual code with the error is above, I missed a Bracket.
Error is still present though.

From Excel Specifications and limits:

Nested levels of functions 7

You have 8 nested levels.


--ron

 

[Corey]

Thanks for the reply guys,
So what options do i have if there is too many nestings?
Is there a different formula syntax i can use?

 

[Guest]

Perhaps this:

=If(C5 = "Delta", If(And(C7>V2, C7<W2, C8>W2), 0.5, If(And(C7<V2, C8<V2), 1,
If(And(C7<V2, C8>=W2), 0.5, 0))), If(C5 = "Non U/G", If(And(C7<V2, C8>V2,
C8<=W2), 0.5, 0), If(And(C7>V2, C7<W2, C8>W2), 0.75, If(And(C7<V2, C8<V2),
1.5, If(And(C7<V2, C8>=W2), 0.75, IF(And(C7<V2, C8>V2, C8<=W2), 0.75, 0))))))

I believe the above reconstructed formula meets the logic you specified. It
has one less nesting level and therefore should work.

Regards,
Greg