More than 7 nested IF(AND...

[Guest]

HELP!!!
I've got 8 nested IF(AND) staements in my formula, please help! Is there a
simpler way of doing this???

I need to return the percentage margin for the lowest selling price. Y, AQ,
AK, AE are the 4 selling prices and P is the standard cost which needs to be
used in the margin calculation. BUT if P=0 then use the old standard cost
value which is column R. This formula was fine until I had to include the IFs
for the R and P column problem.

=IF(AND($Y2>0,$P2>0,$AQ2>0,$AK2>0,$AE2>0),((($AQ2-$P2))/$AQ2),IF(AND($Y2>0,$P2>0,$AQ2=0,$AK2>0,$AE2>0),((($AK2-$P2))/$AK2),IF(AND($Y2>0,$P2>0,$AQ2=0,$AK2=0,$AE2>0),((($AE2-$P2))/$AE2),IF(AND($Y2>0,$P2>0,$AQ2=0,$AK2=0,$AE2=0),((($Y2-$P2))/$Y2),IF(AND($Y2>0,$P2=0,$AQ2>0,$AK2>0,$AE2>0),((($AQ2-$R2))/$AQ2),IF(AND($Y2>0,$P2=0,$AQ2=0,$AK2>0,$AE2>0),((($AK2-$R2))/$AK2),IF(AND($Y2>0,$P2=0,$AQ2=0,$AK2=0,$AE2>0),((($AE2-$R2))/$AE2),IF(AND($Y2>0,$P2=0,$AQ2=0,$AK2=0,$AE2=0),((($Y2-$R2))/$Y2),0))))))))

Thanks!

 

[Guest]

I've solved this now thanks to Chip Pearsons website...

Solution: name the formulas and use 1 IF function in the cell…

StdCostP
=IF(AND($Y2>0,$P2>0,$AQ2>0,$AK2>0,$AE2>0),((($AQ2-$P2))/$AQ2),IF(AND($Y2>0,$P2>0,$AQ2=0,$AK2>0,$AE2>0),((($AK2-$P2))/$AK2),IF(AND($Y2>0,$P2>0,$AQ2=0,$AK2=0,$AE2>0),((($AE2-$P2))/$AE2),IF(AND($Y2>0,$P2>0,$AQ2=0,$AK2=0,$AE2=0),((($Y2-$P2))/$Y2),0))))

StdCostR
=IF(AND($Y2>0,$P2=0,$AQ2>0,$AK2>0,$AE2>0),((($AQ2-$R2))/$AQ2),IF(AND($Y2>0,$P2=0,$AQ2=0,$AK2>0,$AE2>0),((($AK2-$R2))/$AK2),IF(AND($Y2>0,$P2=0,$AQ2=0,$AK2=0,$AE2>0),((($AE2-$R2))/$AE2),IF(AND($Y2>0,$P2=0,$AQ2=0,$AK2=0,$AE2=0),((($Y2-$R2))/$Y2),0))))

And enter in the cell…
IF(StdCostP,StdCostP,StdCostR)