help with nested IF's

[Dr. Harvey Waxman]

I don't get the expected result. Can you see why?

If(cell A is not blank then
see if cell X is greater than the result of this calculation.
if so then cell X, otherwise
if cell X is less than cell Y
then check cell W
if cell W equal "R", do this calculation
otherwise do this calculation.

The answer is always cell X if Cell A isn't blank

this is the formula


=IF(P9644<>"",
IF(M9644>N9644/1000*$N$5,M9644,
IF(B9644="R",M9644*(1+$N$4),M9644*(1+$N$3))))

Thanks

Harvey

 

[JE McGimpsey]

I don't get the expected result. Can you see why?

If(cell A is not blank then
see if cell X is greater than the result of this calculation.
if so then cell X, otherwise
if cell X is less than cell Y
then check cell W
if cell W equal "R", do this calculation
otherwise do this calculation.

The answer is always cell X if Cell A isn't blank

this is the formula


=IF(P9644<>"",
IF(M9644>N9644/1000*$N$5,M9644,
IF(B9644="R",M9644*(1+$N$4),M9644*(1+$N$3))))

Your text description is of the form

=IF(A<>"", IF(X>thiscalc, X, IF(X<Y, IF(W="R", thiscalc, thiscalc),
FALSE)), FALSE)

Note that you don't give a description for what should happen if A is
not blank, nor if A is not blank, X<=thiscalc, and X>=Y.

OTOH, your formula is of the form

=IF(A<>"", IF(X>thiscalc, X, IF(W="R", newcalc1, newcalc2)), FALSE)

which doesn't include a cell Y, so I think you may need to refine the
logic of your algorithm.

However, if I substitute newcalc1 and newcalc2 for the second and third
thiscalc in your description (otherwise the comparison of W="R" is
meaningless), I get:

=IF(A<>"", IF(X>thiscalc, X, IF(X<Y, IF(W="R", newcalc1, newcalc2),
FALSE)), FALSE)

which, substituting for the values you have in your formula:

=IF(P9644<>"", IF(M9644> N9644/1000*$N$5, M9644, IF(M9644<Y,
IF(B9644="R", M9644*(1+$N$4), M9644*(1+$N$3)), FALSE)), FALSE)

Note that Y is still undefined.