More than 16 nested IFs!!!!

[Guest]

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($P2>0,$AA2>0),((($AA2-$P2))/$AA2),IF(AND($P2=0,$AA2>0),((($AA2-$Q2))/$AA2),IF(AND($P2>0,$AJ2>0),((($AJ2-$P2))/$AJ2),IF(AND($P2=0,$AJ2>0),((($AJ2-$Q2))/$AJ2),IF(AND($P2>0,$AS2>0),((($AS2-$P2))/$AS2),IF(AND($P2=0,$AS2>0),((($AS2-$Q2))/$AS2),IF(AND($P2>0,$BB2>0),((($BB2-$P2))/$BB2),IF(AND($P2=0,$BB2>0),((($BB2-$Q2))/$BB2),IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2>0),((($AY2-$P2))/$AY2),IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2=0),((($AP2-$P2))/$AP2),IF(AND,($P2>0,$X2>0,$AG2>0,$AP2=0,$AY2=0),((($AG2-$P2))/$AG2),IF(AND($P2>0,$X2>0,$AG2=0,$AP2=0,$AY2=0),((($X2-$P2))/$X2),IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2>0),((($AY2-$Q2))/$AY2),IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2=0),((($AP2-$Q2))/$AP2),IF(AND($P2=0,$X2>0,$AG2>0,$AP2=0,$AY2=0),((($AG2-$Q2))/$AG2),IF(AND($P2=0,$X2>0,$AG2=0,$AP2=0,$AY2=0),((($X2-$Q2))/$X2),0))))))))))))))))

 

[Harlan Grove]

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($P2>0,$AA2>0),
((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),
((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$AJ2>0),
((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),
((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AS2>0),
((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),
((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$BB2>0),
((($BB2-$P2))/$BB2),
IF(AND($P2=0,$BB2>0),
((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$P2))/$AY2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$P2))/$AP2),
IF(AND,($P2>0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$P2))/$AG2),
IF(AND($P2>0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$Q2))/$AY2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$Q2))/$AP2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$Q2))/$AG2),
IF(AND($P2=0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$Q2))/$X2),
0
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

All conditions check either P2>0 or P2=0. If P2<0, the result will
always be 0. If P2>0, 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($P2>0,$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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),0,
1-IF($P2>0,$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.

 

[Guest]

Wow what a reply! Thanks Harlan I'll try this out on Monday and let you know
how it goes.

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($P2>0,$AA2>0),
((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),
((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$AJ2>0),
((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),
((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AS2>0),
((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),
((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$BB2>0),
((($BB2-$P2))/$BB2),
IF(AND($P2=0,$BB2>0),
((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$P2))/$AY2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$P2))/$AP2),
IF(AND,($P2>0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$P2))/$AG2),
IF(AND($P2>0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$Q2))/$AY2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$Q2))/$AP2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$Q2))/$AG2),
IF(AND($P2=0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$Q2))/$X2),
0
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

All conditions check either P2>0 or P2=0. If P2<0, the result will
always be 0. If P2>0, 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($P2>0,$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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),0,
1-IF($P2>0,$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.

 

[Guest]

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

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($P2>0,$AA2>0),
((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),
((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$AJ2>0),
((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),
((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AS2>0),
((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),
((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$BB2>0),
((($BB2-$P2))/$BB2),
IF(AND($P2=0,$BB2>0),
((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$P2))/$AY2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$P2))/$AP2),
IF(AND,($P2>0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$P2))/$AG2),
IF(AND($P2>0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$Q2))/$AY2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$Q2))/$AP2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$Q2))/$AG2),
IF(AND($P2=0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$Q2))/$X2),
0
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

All conditions check either P2>0 or P2=0. If P2<0, the result will
always be 0. If P2>0, 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($P2>0,$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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),0,
1-IF($P2>0,$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.

 

[Guest]

WAIT!!! I've just tested this a bit more thouroughly...

If a new selling price is entered when the selling price for that price
break was previously 0, then the lowest selling price still shows as the
previous price break (it goes back too far from right to left)...

EG If X2>0 and AA2>0, AG2=0 and AJ2>0, lowest margin is displayed as value
in AA2 but should be AJ2

Can we alter this formula to pick up the first number in the specified cells
working right to left (or is that what we are supposed to have?)

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

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($P2>0,$AA2>0),
((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),
((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$AJ2>0),
((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),
((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AS2>0),
((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),
((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$BB2>0),
((($BB2-$P2))/$BB2),
IF(AND($P2=0,$BB2>0),
((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$P2))/$AY2),
IF(AND($P2>0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$P2))/$AP2),
IF(AND,($P2>0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$P2))/$AG2),
IF(AND($P2>0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2>0),
((($AY2-$Q2))/$AY2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2>0,$AY2=0),
((($AP2-$Q2))/$AP2),
IF(AND($P2=0,$X2>0,$AG2>0,$AP2=0,$AY2=0),
((($AG2-$Q2))/$AG2),
IF(AND($P2=0,$X2>0,$AG2=0,$AP2=0,$AY2=0),
((($X2-$Q2))/$X2),
0
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)

All conditions check either P2>0 or P2=0. If P2<0, the result will
always be 0. If P2>0, 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($P2>0,$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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),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(X2>0,AA2>0,AG2>0,AJ2>0,AP2>0,AS2>0,AY2>0,BB2>0))),0,
1-IF($P2>0,$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.

 

[Harlan Grove]

Meltad wrote...
....

EG If X2>0 and AA2>0, AG2=0 and AJ2>0, lowest margin is displayed as value
in AA2 but should be AJ2

....

My formula would use AA2 in this case because *YOUR* original formula
would use AA2 in this case. That is, *YOUR* original formula begins

=IF(AND($P2>0,$AA2>0),
((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),
((($AA2-$Q2))/$AA2),

If AA2>0, your original formula wouldn't check whether any other value
in cells X2, AG2, AJ2, AP2, AS2, AY5, BB2 is greater than zero or not.
If that's not what you intended, then your original formula was wrong,
and you need to provide corrected specs.

 

[Guest]

Hi Harlan,
You're right, I think I've got the order wrong...
I need to do the calculation using firstly P or then Q if P=0.
And to use the first value from right to left in the cells BB, AY, AS, AP,
AJ, AG, AA, X to do the rest of the calculation. Here are the corrected IFs
(I think!!)...

=
IF(AND($P2>0,$BB2>0),((($BB2-$P2))/BB2),
IF(AND($P2=0,$BB2>0),((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$AY2>0),((($AY2-$P2))/$AY2),
IF(AND($P2=0,$AY2>0),((($AY2-$Q2))/$AY2),
IF(AND($P2>0,$AS2>0),((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$AP2>0),((($AP2-$P2))/$AP2),
IF(AND($P2=0,$AP2>0),((($AP2-$Q2))/$AP2),
IF(AND($P2>0,$AJ2>0),((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AG2>0),((($AG2-$P2))/$AG2),
IF(AND($P2=0,$AG2>0),((($AG2-$Q2))/$AG2),
IF(AND($P2>0,$AA2>0),((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$X2>0),((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0),((($X2-$Q2))/$X2),0))))))))))))))))

 

[Harlan Grove]

Meltad wrote...
....

I need to do the calculation using firstly P or then Q if P=0.
And to use the first value from right to left in the cells BB, AY, AS, AP,
AJ, AG, AA, X to do the rest of the calculation. Here are the corrected IFs ....
[reformatted]

IF(AND($P2>0,$BB2>0),
((($BB2-$P2))/BB2),
IF(AND($P2=0,$BB2>0),
((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$AY2>0),
((($AY2-$P2))/$AY2),

....
IF(AND($P2=0,$AY2>0),((($AY2-$Q2))/$AY2),
IF(AND($P2>0,$AS2>0),((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$AP2>0),((($AP2-$P2))/$AP2),
IF(AND($P2=0,$AP2>0),((($AP2-$Q2))/$AP2),
IF(AND($P2>0,$AJ2>0),((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AG2>0),((($AG2-$P2))/$AG2),
IF(AND($P2=0,$AG2>0),((($AG2-$Q2))/$AG2),
IF(AND($P2>0,$AA2>0),((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$X2>0),((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0),((($X2-$Q2))/$X2),0))))))))))))))))

This makes it easier. Again, use P2 when it's greater than zero, use Q2
when P2 equals zero, and return zero when P2 is less than zero. This is
completely separate from the other comparisons.

Then, use the rightmost positive value from the cells BB2, AY2, AS2,
AP2, AJ2, AG2, AA2 and X2. These now follow a single, well-defined
rule, so no more need for OFFSET.

=IF(AND($P2>=0,SUMPRODUCT(--(MOD(COLUMN($X2:$BB2),9)={6;0}))),
1-IF($P2>0,$P2,$Q2)/LOOKUP(3,MATCH(MOD(COLUMN($X2:$BB2),9),{6;0},0)
/($X2:$BB2>0),$X2:$BB2),0)

 

[Guest]

Thanks Harlan, works great, sorry about over-complicating it before!
Just one thing... I get #N/A in the cell when all values are 0 but it looks
as though the formula should display a zero if the IF statement is false...
how can I change #NA to 0?

Meltad wrote...
....

I need to do the calculation using firstly P or then Q if P=0.
And to use the first value from right to left in the cells BB, AY, AS, AP,
AJ, AG, AA, X to do the rest of the calculation. Here are the corrected IFs ....
[reformatted]

IF(AND($P2>0,$BB2>0),
((($BB2-$P2))/BB2),
IF(AND($P2=0,$BB2>0),
((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$AY2>0),
((($AY2-$P2))/$AY2),

....
IF(AND($P2=0,$AY2>0),((($AY2-$Q2))/$AY2),
IF(AND($P2>0,$AS2>0),((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$AP2>0),((($AP2-$P2))/$AP2),
IF(AND($P2=0,$AP2>0),((($AP2-$Q2))/$AP2),
IF(AND($P2>0,$AJ2>0),((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AG2>0),((($AG2-$P2))/$AG2),
IF(AND($P2=0,$AG2>0),((($AG2-$Q2))/$AG2),
IF(AND($P2>0,$AA2>0),((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$X2>0),((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0),((($X2-$Q2))/$X2),0))))))))))))))))

This makes it easier. Again, use P2 when it's greater than zero, use Q2
when P2 equals zero, and return zero when P2 is less than zero. This is
completely separate from the other comparisons.

Then, use the rightmost positive value from the cells BB2, AY2, AS2,
AP2, AJ2, AG2, AA2 and X2. These now follow a single, well-defined
rule, so no more need for OFFSET.

=IF(AND($P2>=0,SUMPRODUCT(--(MOD(COLUMN($X2:$BB2),9)={6;0}))),
1-IF($P2>0,$P2,$Q2)/LOOKUP(3,MATCH(MOD(COLUMN($X2:$BB2),9),{6;0},0)
/($X2:$BB2>0),$X2:$BB2),0)

 

[Guest]

I've just copied and pasted the formula in again and now I have #VALUE when
all cells are 0!! This doesn't really matter I just want it for tidy-ness I
guess!

Meltad wrote...
....

I need to do the calculation using firstly P or then Q if P=0.
And to use the first value from right to left in the cells BB, AY, AS, AP,
AJ, AG, AA, X to do the rest of the calculation. Here are the corrected IFs ....
[reformatted]

IF(AND($P2>0,$BB2>0),
((($BB2-$P2))/BB2),
IF(AND($P2=0,$BB2>0),
((($BB2-$Q2))/$BB2),
IF(AND($P2>0,$AY2>0),
((($AY2-$P2))/$AY2),

....
IF(AND($P2=0,$AY2>0),((($AY2-$Q2))/$AY2),
IF(AND($P2>0,$AS2>0),((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS2>0),((($AS2-$Q2))/$AS2),
IF(AND($P2>0,$AP2>0),((($AP2-$P2))/$AP2),
IF(AND($P2=0,$AP2>0),((($AP2-$Q2))/$AP2),
IF(AND($P2>0,$AJ2>0),((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ2>0),((($AJ2-$Q2))/$AJ2),
IF(AND($P2>0,$AG2>0),((($AG2-$P2))/$AG2),
IF(AND($P2=0,$AG2>0),((($AG2-$Q2))/$AG2),
IF(AND($P2>0,$AA2>0),((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA2>0),((($AA2-$Q2))/$AA2),
IF(AND($P2>0,$X2>0),((($X2-$P2))/$X2),
IF(AND($P2=0,$X2>0),((($X2-$Q2))/$X2),0))))))))))))))))

This makes it easier. Again, use P2 when it's greater than zero, use Q2
when P2 equals zero, and return zero when P2 is less than zero. This is
completely separate from the other comparisons.

Then, use the rightmost positive value from the cells BB2, AY2, AS2,
AP2, AJ2, AG2, AA2 and X2. These now follow a single, well-defined
rule, so no more need for OFFSET.

=IF(AND($P2>=0,SUMPRODUCT(--(MOD(COLUMN($X2:$BB2),9)={6;0}))),
1-IF($P2>0,$P2,$Q2)/LOOKUP(3,MATCH(MOD(COLUMN($X2:$BB2),9),{6;0},0)
/($X2:$BB2>0),$X2:$BB2),0)

 

[Harlan Grove]

Meltad wrote...

Thanks Harlan, works great, sorry about over-complicating it before!
Just one thing... I get #N/A in the cell when all values are 0 but it looks
as though the formula should display a zero if the IF statement is false...
how can I change #NA to 0?

"Harlan Grove" wrote: ....

Sorry, I left out a check. Make that

=IF(AND($P2>=0,SUMPRODUCT((MOD(COLUMN($X2:$BB2),9)={6;0})*($X2:$BB2>0))),
1-IF($P2>0,$P2,$Q2)/LOOKUP(3,MATCH(MOD(COLUMN($X2:$BB2),9),{6;0},0)
/($X2:$BB2>0),$X2:$BB2),0)

 

[Guest]

Thanks Harlan, perfect!

Meltad wrote...

Sorry, I left out a check. Make that

=IF(AND($P2>=0,SUMPRODUCT((MOD(COLUMN($X2:$BB2),9)={6;0})*($X2:$BB2>0))),
1-IF($P2>0,$P2,$Q2)/LOOKUP(3,MATCH(MOD(COLUMN($X2:$BB2),9),{6;0},0)
/($X2:$BB2>0),$X2:$BB2),0)