average low 10 of last 20 entries

[Tom]

I have a continuing list of numeric entries. At any one time I want to be
able to cvalculate the average of the lowest 10 of the most recent 20
entries. I'm using Excel 2002.

 

[Bernard Liengme]

Assuming A1 has a label and there are no empty cells in the column of
numbers below:
This finds the average of the last 20
=AVERAGE(OFFSET($A$1,COUNTA(A:A)-1,0,-20,1))
and this finds average of the smallest 10 in the last 20
=AVERAGE(SMALL(OFFSET($A$1,COUNTA(A:A)-1,0,-20,1),{1,2,3,4,5,6,7,8,9,10}))
I expect {1,2,3...} could be replaced by ROW(something) but I had no luck
best wishes

 

[Bob Phillips]

=AVERAGE(IF(ISNUMBER(MATCH(A1:A20,SMALL(A1:A20,ROW(INDIRECT("1:10"))),0)),A1
:A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Not sure that this is sound though. Say you have the numbers
1-8,10,10,10,12-20. The 10 smallest are 1-8,10,10, which averages at 5.6
this returns 6 because it includes all of the 10s.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Domenic]

Assuming that A2:A100 contains the data, try the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try...

=AVERAGE(SMALL(INDEX(A2:A100,MATCH(BigNum,A2:A100)-B2+1):INDEX(A2:A100,MA
TCH(BigNum,A2:A100)),{1,2,3,4,5,6,7,8,9,10}))

....where B2 contains 20.

Hope this helps!

 

[Bob Phillips]

=AVERAGE(SMALL(OFFSET($A$1,COUNTA(A:A)-1,0,-20,1),{1,2,3,4,5,6,7,8,9,10}))
I expect {1,2,3...} could be replaced by ROW(something) but I had no luck
best wishes

=AVERAGE(SMALL(OFFSET($A$1,COUNTA(A:A)-1,0,-20,1),ROW(INDIRECT("1:10"))))

 

[Bernard Liengme]

Copying from Bob
=AVERAGE(SMALL(OFFSET(A1,COUNTA(A:A)-1,0,-20,1),ROW(INDIRECT("1:10"))))
Not I was inconsistent with $ before, use them before every A or not at all

 

[Bernard Liengme]

Thanks, I saw that in you reply to the OP

 

[Alan Beban]

=AVERAGE(SMALL(OFFSET($A$1,COUNTA(A:A)-1,0,-20,1),ROW(INDIRECT("1:10"))))

ROW(1:10) seems to work as well as ROW(INDIRECT("1:10"))

Alan Beban

 

[Bob Phillips]

Yeah, but is it susceptible to a user inserting a row within or before that
range.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[romelsb]

Hi guys....it seems we're discussing based on TOM statements...let him
specify what he means by "RECENT". Is it recent by the date, regardless on
which row he encoded the entries...try our luck next time !!!