Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")))) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")))) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate a 30-day moving average based on the last x number of entries and date | Excel Worksheet Functions | |||
in a pivot table, can the average include blank entries? | Excel Discussion (Misc queries) | |||
Average a set of figures which ignores 0 entries | Excel Worksheet Functions | |||
Average of column entries | Excel Discussion (Misc queries) | |||
Any way to calculate an average for more than 30 entries? | Excel Worksheet Functions |