Formula error.

[Charlie]

I have the following formula in cell M7,

=IF(AND($I$3="n",$L$3="n"),0,
IF(AND($I$3="n",$L$3="y"),IF(AND(K7>1,K7<cap),ROUND
(K7*srate1,2),
IF(AND($I$3="n",$L$3="y"),IF(K7>cap,ROUND(cap*srate1,2),
IF(AND($I$3="y",$L$3="y"),ROUND(K7*csrate1,2),0))))))

The idea is to have the user answer two Yes or No
questions in cells I3 and L3.

The combination for a 'n' and a 'n' work fine.
The two combinations for a 'n' and a 'y' work fine
The 'y' and 'y' combination gives a result of FALSE.

The 'cap', 'srate1', and 'csrate1' ranges names are
correct.

What am I missing?

 

[Frank Kabel]

Hi
not fully tested but try
=IF(AND($I$3="n",$L$3="n"),0,IF(AND($I$3="n",$L$3="y"),IF(AND(K7>1,K7<c
ap),ROUND(K7*srate1,2), "missing result for K7<1 or >cap"),
IF(AND($I$3="n",$L$3="y"),IF(K7>cap,ROUND(cap*srate1,2),"missing result
for K7<cap"),IF(AND($I$3="y",$L$3="y"),ROUND(K7*csrate1,2),0))))

 

[Guest]

Thank you, it works.

-----Original Message-----
Hi
not fully tested but try
=IF(AND($I$3="n",$L$3="n"),0,IF(AND($I$3="n",$L$3="y"),IF (AND(K7>1,K7<c
ap),ROUND(K7*srate1,2), "missing result for K7<1 or
cap"),
IF(AND($I$3="n",$L$3="y"),IF(K7>cap,ROUND (cap*srate1,2),"missing result
for K7<cap"),IF(AND($I$3="y",$L$3="y"),ROUND (K7*csrate1,2),0))))



--
Regards
Frank Kabel
Frankfurt, Germany


.