More than 16 nested IFs!!!!

M

Meltad

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))))))))))))))))
 
H

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
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
Click to expand...

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.
 
M

Meltad

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

Harlan Grove said:
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
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
Click to expand...

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.
Click to expand...
 
M

Meltad

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


Harlan Grove said:
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
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
Click to expand...

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.
Click to expand...
 
M

Meltad

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?)





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


Harlan Grove said:
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
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
Click to expand...

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.
Click to expand...
Click to expand...
 
H

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
Click to expand...
....

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.
 
M

Meltad

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))))))))))))))))
 
H

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),
Click to expand...
....
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))))))))))))))))
Click to expand...

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)
 
M

Meltad

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 said:
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),
Click to expand...
....
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))))))))))))))))
Click to expand...

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)
Click to expand...
 
M

Meltad

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!

Harlan Grove said:
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),
Click to expand...
....
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))))))))))))))))
Click to expand...

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)
Click to expand...
 
H

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: ....
Click to expand...

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)
 
M

Meltad

Thanks Harlan, perfect!

Harlan Grove said:
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)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Easier way to concatenate a row? 8
Nested Ifs VBA, How to 3
More than 7 nested IF(AND... 1
SQL Insert Into Statement 3
Quick VBA Worksheet Change Event or Selection Question: 6
Access_Excel_SaveFile 0
More problems using an IF statement. 3
Code Help 3

Top