Hi all,

I used Chip Pearsons method of overcoming 7 nested IF statements using named
ranges and this worked (http://www.cpearson.com/excel/nested.htm) but now I
have some additions to my formula which makes the total number of IFs 16 -
how do I get over more than 7 IFs in each of my seperate named ranges???

Here is my whole formula!!! It calculates the lowest margin for a range of
selling prices. P and R are new and old standard costs (I need to use R in
the calculation if P is 0), the rest of the cells are 4 different prices
volume breaks, the lowest of which needs to be used in the calculation. To
make matters worse there is a new and old column for each price break and
obviously I need to use the new but revert back to using the old if no new
price is entered! Hence my 16 IFs!!!

=
IF(AND($P20,$AA20),((($AA2-$P2))/$AA2),IF(AND($P2=0,$AA20),((($AA2-$Q2))/$AA2),IF(AND($P20,$AJ20),((($AJ2-$P2))/$AJ2),IF(AND($P2=0,$AJ20),((($AJ2-$Q2))/$AJ2),IF(AND($P20,$AS20),((($AS2-$P2))/$AS2),IF(AND($P2=0,$AS20),((($AS2-$Q2))/$AS2),IF(AND($P20,$BB20),((($BB2-$P2))/$BB2),IF(AND($P2=0,$BB20),((($BB2-$Q2))/$BB2),IF(AND($P20,$X20,$AG20,$AP20,$AY20),((( $AY2-$P2))/$AY2),IF(AND($P20,$X20,$AG20,$AP20,$AY2=0),((( $AP2-$P2))/$AP2),IF(AND,($P20,$X20,$AG20,$AP2=0,$AY2=0),(( ($AG2-$P2))/$AG2),IF(AND($P20,$X20,$AG2=0,$AP2=0,$AY2=0),((( $X2-$P2))/$X2),IF(AND($P2=0,$X20,$AG20,$AP20,$AY20),((($ AY2-$Q2))/$AY2),IF(AND($P2=0,$X20,$AG20,$AP20,$AY2=0),((( $AP2-$Q2))/$AP2),IF(AND($P2=0,$X20,$AG20,$AP2=0,$AY2=0),((( $AG2-$Q2))/$AG2),IF(AND($P2=0,$X20,$AG2=0,$AP2=0,$AY2=0),((( $X2-$Q2))/$X2),0))))))))))))))))