Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible to combine VLOOKUP and IF(AND) functions? | Excel Worksheet Functions | |||
Nested Subtotals in Excel 2002 | Excel Discussion (Misc queries) | |||
nested if(and) functions | Excel Discussion (Misc queries) | |||
Why are my nested sub-totals are displaying incorrectly? | Excel Discussion (Misc queries) | |||
Nested IF(AND is not working | Excel Worksheet Functions |