M
Meltad
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!
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!