How to Average the last 8 GOOD data entries, without VBA, OFFSET, or Array Function?

[wiseone]

Hi All,

Trying to keep my 10MB spreadsheet fast.

I have 52 weeks of data which changes weekly and I need to average the last 8 weeks of good data, preferably without OFFSET (but if I have to I have to), an Array function, or VB.

Good Data: Not=<0, not>=1, not text, not blank.

Or you could say: Good data is between 0 and 1. (For some reason the greater than and less than symbol kept disappearing when I posted this thread).
<gooddata<1<good data<1.

Example:

Week	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52
Data	.2	1	.78	1	.52	1	1	.1	.2	.15	.8		0	.7	1	.99	.58

<tbody>
</tbody>

Would return the average of the last 8 good data cells (green text) = 0.505

Thanks in advance!</gooddata<1<good>

 

[István Hirsch]

This also works:

=SUMPRODUCT(MOD(LARGE(IF(($B2:$R2>0)*($B2:$R2<1),$B2:$R2+COLUMN($B2:$R2),0),{1,2,3,4,5,6,7,8}),1))/8