More than 7 nested IF(AND...
 

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($Y20,$P20,$AQ20,$AK20,$AE20),((($AQ2-$P2))/$AQ2),IF(AND($Y20,$P20,$AQ2=0,$AK20,$AE20),((( $AK2-$P2))/$AK2),IF(AND($Y20,$P20,$AQ2=0,$AK2=0,$AE20),((( $AE2-$P2))/$AE2),IF(AND($Y20,$P20,$AQ2=0,$AK2=0,$AE2=0),((( $Y2-$P2))/$Y2),IF(AND($Y20,$P2=0,$AQ20,$AK20,$AE20),((($ AQ2-$R2))/$AQ2),IF(AND($Y20,$P2=0,$AQ2=0,$AK20,$AE20),((( $AK2-$R2))/$AK2),IF(AND($Y20,$P2=0,$AQ2=0,$AK2=0,$AE20),((( $AE2-$R2))/$AE2),IF(AND($Y20,$P2=0,$AQ2=0,$AK2=0,$AE2=0),((( $Y2-$R2))/$Y2),0))))))))

Thanks!

More than 7 nested IF(AND...
 

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($Y20,$P20,$AQ20,$AK20,$AE20),((($AQ2-$P2))/$AQ2),IF(AND($Y20,$P20,$AQ2=0,$AK20,$AE20),((( $AK2-$P2))/$AK2),IF(AND($Y20,$P20,$AQ2=0,$AK2=0,$AE20),((( $AE2-$P2))/$AE2),IF(AND($Y20,$P20,$AQ2=0,$AK2=0,$AE2=0),((( $Y2-$P2))/$Y2),0))))

StdCostR:
=IF(AND($Y20,$P2=0,$AQ20,$AK20,$AE20),((($AQ2-$R2))/$AQ2),IF(AND($Y20,$P2=0,$AQ2=0,$AK20,$AE20),((( $AK2-$R2))/$AK2),IF(AND($Y20,$P2=0,$AQ2=0,$AK2=0,$AE20),((( $AE2-$R2))/$AE2),IF(AND($Y20,$P2=0,$AQ2=0,$AK2=0,$AE2=0),((( $Y2-$R2))/$Y2),0))))

And enter in the cell€¦
IF(StdCostP,StdCostP,StdCostR)



"Meltad" wrote:

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($Y20,$P20,$AQ20,$AK20,$AE20),((($AQ2-$P2))/$AQ2),IF(AND($Y20,$P20,$AQ2=0,$AK20,$AE20),((( $AK2-$P2))/$AK2),IF(AND($Y20,$P20,$AQ2=0,$AK2=0,$AE20),((( $AE2-$P2))/$AE2),IF(AND($Y20,$P20,$AQ2=0,$AK2=0,$AE2=0),((( $Y2-$P2))/$Y2),IF(AND($Y20,$P2=0,$AQ20,$AK20,$AE20),((($ AQ2-$R2))/$AQ2),IF(AND($Y20,$P2=0,$AQ2=0,$AK20,$AE20),((( $AK2-$R2))/$AK2),IF(AND($Y20,$P2=0,$AQ2=0,$AK2=0,$AE20),((( $AE2-$R2))/$AE2),IF(AND($Y20,$P2=0,$AQ2=0,$AK2=0,$AE2=0),((( $Y2-$R2))/$Y2),0))))))))

Thanks!