Thanks Harlan, this works perfectly! Just what I was after, and thanks for
all the explanation aswell :-)


"Harlan Grove" wrote:

Meltad wrote...
....
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!!!

[reformatted]
=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
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

All conditions check either P20 or P2=0. If P2<0, the result will
always be 0. If P20, use P2 in the result expression; otherwise, if
P2=0, use Q2 in the result expression. Which means that the result
expression could be rewritten [simplifying (((a-x))/a) to 1-x/a] as

1-IF($P20,$P2,$Q2)/a

The rest of the logic decides what <a should be. It's either the first
positive value in AA2, AJ2, AS2, BB2, or if none of them are positive,
the last positive value in X2, AG2, AP2, AY2, or if none of them are
positive, 0.

So the result is zero if either P2<0 or none of X2, AA2, AG2, AJ2, AP2,
AS2, AY2, BB2 are positive. That can be expressed as

=IF(OR($P2<0,NOT(OR(X20,AA20,AG20,AJ20,AP20,A S20,AY20,BB20))),0,...)

Finding the intended positive value can be done with some trickery
using LOOKUP and OFFSET.

LOOKUP(2,1/(N(OFFSET($X2,0,{0,9,18,27,30,21,12,3}))0),
N(OFFSET($X2,0,{0,9,18,27,30,21,12,3})))

The trick here being that LOOKUP will return values in order from RIGHT
to LEFT, so the value you want first if it's positive is the rightmost
one referenced by OFFSET (the 3 in the array constant, or cell AA2),
and the one you want last if it's the only one of them that's positive
is the leftmost one referenced by OFFSET (the 0 in the array constant,
or cell X2).

Tie it all together.

=IF(OR($P2<0,NOT(OR(X20,AA20,AG20,AJ20,AP20,A S20,AY20,BB20))),0,
1-IF($P20,$P2,$Q2)/LOOKUP(2,1/(N(OFFSET($X2,0,{0,9,18,27,30,21,12,3}))0),
N(OFFSET($X2,0,{0,9,18,27,30,21,12,3}))))

Any complicated nested IF construct can be reduced to a lookup,
possibly with even less clarity, but definitely with fewer nested
function call levels.