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>

 

[kvsrinivasamurthy]

This smple formula works

=SUMPRODUCT((B2:R2>0)*(B2:R2<1)*(B2:R2)*(COLUMN(B2:R2)>=LARGE((B2:R2>0)*(B2:R2<1)*COLUMN(B2:R2),8)))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(COLUMN(B2:R2) >= LARGE(IF(B2:R2 > 0,IF(B2:R2 < 1,COLUMN(B2:R2))),MIN(8,SUM(IF(B2:R2 > 0,IF(B2:R2 < 1,1))))),IF(B2:R2 > 0,IF(B2:R2 < 1,B2:R2))))

You can replace the SUM bit with a COUNTIFS bit if so desired...

Control+shift+enter, not just enter:

=AVERAGE(IF(COLUMN(B2:R2) >= LARGE(IF(B2:R2 > 0,IF(B2:R2 < 1,COLUMN(B2:R2))),MIN(8,COUNTIFS(B2:R2,">0",B2:R2,"<1"))),IF(B2:R2 > 0,IF(B2:R2 < 1,B2:R2))))

 

[stunnrock]

Okay, this avoids volatile functions, and allows for less than 8 good values, without needing array entry:

=SUMPRODUCT(LOOKUP(LARGE(($B2:$R2>0)*($B2:$R2<1)*COLUMN($B2:$R2),ROW(A1:INDEX(A:A,MIN(8,SUMPRODUCT(($B2:$R2>0)*($B2:$R2<1)))))-ROW(A1)+1),COLUMN($B2:$R2),$B2:$R2))/MIN(8,SUMPRODUCT(($B2:$R2>0)*($B2:$R2<1)))

 

[stunnrock]

However, it may recalculate whenever any cell in column A is changed, so it may be best to change ROW(A1:INDEX(A:A to use a column that is outside of any data entry area.

 

[stunnrock]

Keep in mind that if the column used with the INDEX is deleted, the formula will fail.

I still contend that INDIRECT is the better option, as its only caveat is that it may affect performance in a very large data set. In the case of less than 8 good cells, a formula using INDIRECT would be:

=SUMPRODUCT(LOOKUP(LARGE(($B2:$R2>0)*($B2:$R2<1)*COLUMN($B2:$R2),ROW(INDIRECT("1:"&MIN(8,SUMPRODUCT(($B2:$R2>0)*($B2:$R2<1)))))),COLUMN($B2:$R2),$B2:$R2))/MIN(8,SUMPRODUCT(($B2:$R2>0)*($B2:$R2<1)))

 

[stunnrock]

However, it may recalculate whenever any cell in column A is changed, so it may be best to change ROW(A1:INDEX(A:A to use a column that is outside of any data entry area.

Whoops, don't forget to change the -ROW(A1) part to use the same column too.

 

[wiseone]

Keep in mind that if the column used with the INDEX is deleted, the formula will fail.

I still contend that INDIRECT is the better option, as its only caveat is that it may affect performance in a very large data set. In the case of less than 8 good cells, a formula using INDIRECT would be:

=SUMPRODUCT(LOOKUP(LARGE(($B2:$R2>0)*($B2:$R2<1)*COLUMN($B2:$R2),ROW(INDIRECT("1:"&MIN(8,SUMPRODUCT(($B2:$R2>0)*($B2:$R2<1)))))),COLUMN($B2:$R2),$B2:$R2))/MIN(8,SUMPRODUCT(($B2:$R2>0)*($B2:$R2<1)))

Thanks again Stunnrock!! You've hit the nail on the head. This solution works perfectly for my needs and meets all criteria.

This is a one cell per sheet calculation and does not get copied to other rows or columns. With that in mind, i'm not concerned about inserting rows or columns at this time, but I liked the idea of using {1,2,3,4,5,6,7,8} per another suggestion in the formula so it is not subject to errors in future.

Is the above formula subject to error if rows or columns get inserted anywhere? If yes, is there a way to error-proof against columns or row additions/deletions, so it maintains an average of 8 cells (or less if missing good data)?

 

[Aladin Akyurek]

I'd advise the non-volatile the post #22 suggestion...

 

[stunnrock]

You're welcome wiseone. That method is not affected by inserting, or deleting, rows or columns.


I would also prefer using {1;2;3;4;5;6;7;8} but that would raise errors when there were less than 8 valid values, and isn't so easily adapted if say the requirements changed to wanting 20 rather than 8.


It also has the benefit of being dynamic. e.g. if you arranged for a cell to contain the number of valid values to consider, you could replace the 8 (which appears twice in the formula) with a reference to that cell.

 

[wiseone]

I like it! Thanks again! Just gotta say it, I love the internet.