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" wrote:

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($P20,$BB20),
((($BB2-$P2))/BB2),
IF(AND($P2=0,$BB20),
((($BB2-$Q2))/$BB2),
IF(AND($P20,$AY20),
((($AY2-$P2))/$AY2),
....
IF(AND($P2=0,$AY20),((($AY2-$Q2))/$AY2),
IF(AND($P20,$AS20),((($AS2-$P2))/$AS2),
IF(AND($P2=0,$AS20),((($AS2-$Q2))/$AS2),
IF(AND($P20,$AP20),((($AP2-$P2))/$AP2),
IF(AND($P2=0,$AP20),((($AP2-$Q2))/$AP2),
IF(AND($P20,$AJ20),((($AJ2-$P2))/$AJ2),
IF(AND($P2=0,$AJ20),((($AJ2-$Q2))/$AJ2),
IF(AND($P20,$AG20),((($AG2-$P2))/$AG2),
IF(AND($P2=0,$AG20),((($AG2-$Q2))/$AG2),
IF(AND($P20,$AA20),((($AA2-$P2))/$AA2),
IF(AND($P2=0,$AA20),((($AA2-$Q2))/$AA2),
IF(AND($P20,$X20),((($X2-$P2))/$X2),
IF(AND($P2=0,$X20),((($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($P20,$P2,$Q2)/LOOKUP(3,MATCH(MOD(COLUMN($X2:$BB2),9),{6;0},0)
/($X2:$BB20),$X2:$BB2),0)