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>

 

[admiral100]

Hi,

I don't think there is a possibility without an array formula ...
Unless you're ready to add helper columns ...

This is an array one Ctrl+Shift+Enter NOT just Enter

In A4 =AVERAGE(IFERROR(LOOKUP(LARGE(((IF(A2:$R$2<>"",IF(A2:$R$2>0,IF(A2:$R$2<1,COLUMN(A2:$R$2)))))),{1,2,3,4,5,6,7,8}),COLUMN(A4:$R$4),A2:$R$2),""))

 

[stunnrock]

Try this:

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

 

[admiral100]

stunnrock Very nice like it

I would change the INDIRECT to ROW(A1:A8).....

 

[Jonmo1]

To avoid arrays and offsets and such, you need a helper column (or row in this case)

Assuming the table you posted begins in A1
In B3 and dragged right, put
=AND(COUNTIFS(B2:$R2,">0",B2:$R2,"<1")<=8,B2>0,B2<1)

Then use
=AVERAGEIF(B3:R3,TRUE,B2:R2)

 

[stunnrock]

If you change the INDIRECT to ROW(A1:A8) and then a row gets inserted, you'll suddenly have ROW(A1:A9) where you don't want it.

 

[wiseone]

Thank all, I'd prefer not to have helper rows if possible, but am not opposed to it if I have to. I'll give stunrock's method a shot first then jonmo1 if it doesn't work.

Thanks!

 

[Jonmo1]

If you change the INDIRECT to ROW(A1:A8) and then a row gets inserted, you'll suddenly have ROW(A1:A9) where you don't want it.

Why wouldn't you want that ? That's exactly what I would hope would happen.

I think what you meant was if you inserted a row 'above' A1, you would then have ROW(A2:A9)
In that case, you can do
ROW(A1:A8)-ROW(A1)+1

 

[stunnrock]

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))

 

[wiseone]

Try this:

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

This method is missing a check for text (sorry I forgot to put that in my example). how do I add a check in the formula so it does not calculate when text is written in the cell?