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:
....
=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)

Sorry, I left out a check. Make that

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