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>

 

[Jonmo1]

No, if any rows gets inserted anywhere between row 1 and row 8 they would cause issues. Much easier to just use ROW(INDIRECT("1:8",1))

I see it now, you're right. and the -row+1 won't fix it either.

 

[wiseone]

Just bumping a reply in case my comment regarding a check for text was missed.

 

[Jonmo1]

Admiral100's solution (the very first reply) handles text entries and inserting/deleting rows/columns

 

[stunnrock]

=sumproduct(lookup(large(($b2:$r2>0)*($b2:$r2<1)*column($b2:$r2),row(indirect("1:8",1))),column($b2:$r2),$b2:$r2))/min(8,sumproduct(($b2:$r2>0)*($b2:$r2<1)))

 

[wiseone]

Excellent! thanks stunnrock! it works!

 

[Eric W]

No, if any rows gets inserted anywhere between row 1 and row 8 they would cause issues. Much easier to just use ROW(INDIRECT("1:8",1))

You could use {1;2;3;4;5;6;7;8} instead, but of course that doesn't scale as well.

 

[wiseone]

=sumproduct(lookup(large(($b2:$r2>0)*($b2:$r2<1)*column($b2:$r2),row(indirect("1:8",1))),column($b2:$r2),$b2:$r2))/min(8,sumproduct(($b2:$r2>0)*($b2:$r2<1)))

Is there a way to mod this to work so if there is less than 8 weeks good data it still averages?

 

[mikerickson]

No, if any rows gets inserted anywhere between row 1 and row 8 they would cause issues. Much easier to just use ROW(INDIRECT("1:8",1))

If one is going for speed, one should avoid INDIRECT, and other volatile functions like the plague.

 

[stunnrock]

@wiseone, you should find that it already handles less than 8.

 

[Eric W]

@stunnrock, the denominator of your formula handles less than 8, but not the numerator. Specifically, the LARGE function will start throwing errors if there are less than 8 valid numbers. We can adapt your formula by including IFERROR like so:

=SUM(IFERROR(LOOKUP(LARGE(($B2:$R2>0)*($B2:$R2<1)*COLUMN($B2:$R2),{1;2;3;4;5;6;7;8}),COLUMN($B2:$R2),$B2:$R2),0))/MIN(8,SUMPRODUCT(($B2:$R2>0)*($B2:$R2<1)))
and confirm with Control+Shift+Enter.

@wiseone, yes this is an array formula, but it really doesn't take significantly longer than stunnrock's previous version. It does the exact same processing as the previous version, just including IFERROR, which is a fast function (according to Microsoft).