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:

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)