Moving Average Best 5 of last 6 values

[jimrward]

I have a rows of data which contain variable numbers of columns, some with values some without.

What i need to do is take the average of the best 5 from the last six non zero values, providing there are more than 5 columns, if there are only 5 then just the average.

 

[big_mac]

If your information (numbers) begin in cell C2 and extend to column I this formula placed in Cell J2 should meet your needs. other wise I didn't understand your question.

J2:
=SUM(LARGE(C2:I2,{1,2,3,4,5}))/5
it will pick the best 5 and average them

 

[Seti]

big_mac,

I don't think your solution addresses the need to take the largest 5 from the last 6 non-zero values.

 

[eliW]

what do you mean by "best 5"? how do you determin best?

 

[big_mac]

ADVERTISEMENT

Seti,
I do see the error of my calculation.
I am averaging the LARGEST 5
but not of the last 6 non-zero values.
if there are 11 columns displayed as 100,100,100,100,100,50,50,50,50,50,50
the average of the last 6 non-zero should be 50 not 100

eliW,
Forgive my evaluation of LARGE as best, but I normally think Large is best. Please forgive my error in word choice. I have not seen the original poster reply to either of our attempts at a possible solution yet.

 

[Oaktree]

Try:

M2 =SUM(LARGE(INDIRECT("R"&ROW()&"C"&LARGE(((A2:L2>0)*COLUMN(A2:L2)),ROW(INDIRECT("1:"&MIN(COUNTIF(A2:L2,">0"),6)))),0),ROW(INDIRECT("1:"&MIN(COUNTIF(A2:L2,">0"),5)))))/MIN(COUNTIF(A2:L2,">0"),5)

Where A:L is your (largest) range of number inputs.

M2 is an array formula and must be confirmed with CTRL+SHIFT+ENTER instead of just ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar).

Then, copy M2 and paste down through the end of your range.

 

[jimrward]

ADVERTISEMENT

for my purposes I require to look at the last 6 non zero values and select the highest 5

so i could have something like (SUM(A1:F1)-MIN(A1:F1))/5 in simple terms if this was fixed range, now if there were 8 values I would require something like (SUM(D1:I1)-MIN(D1:I1))/5 disregarding A1:C1

so the formula needs to be dynamic and if possible take into account zero values so for instance if in the above F1 was zero the starting cell would be C1

 

[jimrward]

cheers oaktree

thats seems to solve the problem, i was getting all messed up with CELL and OFFSET along with INDIRECT and the like and almost getting there but not quite

 

[Domenic]

To average the 5 largest numbers out of the last 6 non-zero numbers, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(LARGE(IF(COLUMN(A1:I1)>=LARGE(IF(A1:I1>0,COLUMN(A1:I1)),6),IF(A1:I1>0,A1:I1)),{1,2,3,4,5}))

Adjust the range accordingly.

Hope this helps!

 

[Oaktree]

=AVERAGE(LARGE(IF(COLUMN(A1:I1)>=LARGE(IF(A1:I1>0,COLUMN(A1:I1)),6),IF(A1:I1>0,A1:I1)),{1,2,3,4,5}))

Hi, Domenic. I think this fails (#NUM! error) if there are fewer than 6 non-zero numbers. The "providing there are more than 5 columns, if there are only 5 then just the average" part of the OP's request led me to the beast I posted above in event there were rows where there were fewer than 6 (but >=1) cases...