Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default Formula for average of a top 12 numbers in a column of 24 total nu

I have a list of 2 games played per month. I'd like to take the average of
the top 12 games of the 24 listed with out having to resort them (I want to
keep them in the order of the months). Is there a way to do this?

It's giving me a lot of stress digging around and trying to find an answer.
This has had to come up before. Thanks in advance.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default Formula for average of a top 12 numbers in a column of 24 total nu

This will average the top 12 largest numbers


=AVERAGE(LARGE(A1:A24,{1;2;3;4;5;6;7;8;9;10;11;12} ))


=AVERAGE(LARGE(A1:A24,ROW(INDIRECT("1:12"))))


this formula is volatile


or if you never insert rows above the data


=AVERAGE(LARGE(A1:A24,ROW(1:12)))


the 2 latter formulas need to be entered with ctrl + shift & enter





--

Regards,

Peo Sjoblom


"Sean" wrote in message
...
I have a list of 2 games played per month. I'd like to take the average of
the top 12 games of the 24 listed with out having to resort them (I want
to
keep them in the order of the months). Is there a way to do this?

It's giving me a lot of stress digging around and trying to find an
answer.
This has had to come up before. Thanks in advance.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default Formula for average of a top 12 numbers in a column of 24 tota

Awesome! The second works perfectly, thanks!

"Peo Sjoblom" wrote:

This will average the top 12 largest numbers


=AVERAGE(LARGE(A1:A24,{1;2;3;4;5;6;7;8;9;10;11;12} ))


=AVERAGE(LARGE(A1:A24,ROW(INDIRECT("1:12"))))


this formula is volatile


or if you never insert rows above the data


=AVERAGE(LARGE(A1:A24,ROW(1:12)))


the 2 latter formulas need to be entered with ctrl + shift & enter





--

Regards,

Peo Sjoblom


"Sean" wrote in message
...
I have a list of 2 games played per month. I'd like to take the average of
the top 12 games of the 24 listed with out having to resort them (I want
to
keep them in the order of the months). Is there a way to do this?

It's giving me a lot of stress digging around and trying to find an
answer.
This has had to come up before. Thanks in advance.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to average a column of numbers that are greater than 0? JimNColorado Excel Worksheet Functions 13 December 11th 07 02:22 AM
Total instances of the same numbers from one column? Aimcorep Excel Worksheet Functions 2 April 26th 07 06:11 PM
Sum/average numbers in column A dependant on value in column B Sue Excel Worksheet Functions 3 March 29th 06 06:39 PM
Average first 3 numbers in column e.g. 130 in 130/82 gadmire New Users to Excel 6 January 28th 06 02:15 PM
Pivot Table -- Add column to average Grand Total Lynn Excel Worksheet Functions 1 August 30th 05 03:59 PM


All times are GMT +1. The time now is 11:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"