Top ten highest numbers!

  • Thread starter Thread starter rahardin
  • Start date Start date
R

rahardin

Dear All,

I have a column full of dates (column A), and a column full o
associated numbers (column B)

e.g.

Column A Column B
01/01/04 10
04/02/04 20
16/02/03 12
25/12/03 25
16/02/04 9 etc.


I want a formula to average the numbers in column B that correspond t
the 3 most recent dates in column A. (In this case averaging 13). Ho
do I do this? :confused:

New data may be added into Column A or B at any time so in essance i
will be a moving average.

Similarly I would also like to know how to write a formula to give m
just the 2nd most recent date in column A. (In this case, 2nd mos
recent date = 04/02/04). Any ideas? :(

All help much appreciated.

Roger :
 
=AVERAGE(IF(OFFSET(A1,,,COUNT(A:A))>=LARGE(OFFSET
(A1,,,COUNT(A:A)),3),OFFSET(B1,,,COUNT(B:B))))

Array-entered.

HTH
Jason
Atlanta, GA
 
Hi
try something like the following array formula (entered with
CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100>=LARGE(A1:A100,3),B1:B100))
 
Hi Jason
I think this won't work as the list seems to be unsorted (according to
the OP#s example data)
 
Frank, that one sorted it. Many thanks to both of you for an amazingl
quick response.

Roge
 
Dear All,

Same column full of dates (column A), and a column full of associate
numbers (column B)

e.g.

Column A Column B
01/01/04 10
04/02/04 20
16/02/03 12
25/12/03 25
16/02/04 9 etc.


Can Franks fomula be modified to produce a formula to average th
numbers in column B that correspond to the 3 most recent date
_equal_to_or_earlier_than_10/02/04_ in column A, . (In this cas
averaging 18.3333). How do I do this
 
Hi
try the following array formula:
=AVERAGE(IF((A1:A100<=DATE(2004,2,10))*(A1:A100>=LARGE(IF(A1:A100<=DATE
(2004,2,10),A1:A100),3)),B1:B100))
 
Back
Top