average low 10 of last 20 entries
 

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.

average low 10 of last 20 entries
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tom" wrote in message
...
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.

average low 10 of last 20 entries
 

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

"Tom" wrote in message
...
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.

average low 10 of last 20 entries
 

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!

In article ,
Tom wrote:

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.

average low 10 of last 20 entries
 

"Bernard Liengme" wrote in message
...

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

average low 10 of last 20 entries
 

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tom" wrote in message
...
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.

average low 10 of last 20 entries
 

Thanks, I saw that in you reply to the OP
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bob Phillips" wrote in message
...


"Bernard Liengme" wrote in message
...

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

average low 10 of last 20 entries
 

Bob Phillips wrote:
"Bernard Liengme" wrote in message
...

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


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

Alan Beban

average low 10 of last 20 entries
 

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)

"Alan Beban" <unavailable wrote in message
...
Bob Phillips wrote:
"Bernard Liengme" wrote in message
...


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


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

Alan Beban

average low 10 of last 20 entries
 

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


"Domenic" wrote:

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!

In article ,
Tom wrote:

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.