 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006average 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. 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  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >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(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")))) Thanks, I saw that in you reply to the OP SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >> =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(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 Yeah, but is it susceptible to a user inserting a row within or before that range. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > >> =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 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 SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > Assuming A1 has a label and there are no empty cells in the column of > numbers below: [quoted text clipped - 7 lines] >> able to cvalculate the average of the lowest 10 of the most recent 20 >> entries. I'm using Excel 2002. =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. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > 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. 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! > 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. 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 !!! > Assuming that A2:A100 contains the data, try the following... > [quoted text clipped - 20 lines] > > able to cvalculate the average of the lowest 10 of the most recent 20 > > entries. I'm using Excel 2002. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.