Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LCB
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #'s

I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Miguel Zapico
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #'s

You may use this array formula:
=AVERAGE(LARGE(A1:A52,ROW(1:48)))
Enter it with Crt+Shift+Enter

"LCB" wrote:

I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #'s

This will find the average with the four smallest excluded
=(SUM(A1:A52)-SMALL(A1:A52,1)-SMALL(A1:A52,2)-SMALL(A1:A52,3)-SMALL(A1:A52,4))/48
If there are, for example, 7 ones then the average is computed without 4 of
them.
Not sure what you want to do with duplicates.
Are the numbers integer (whole numbers); do you know the range before hand?
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LCB" wrote in message
...
I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LCB
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #

I tried this array: it geave me the highest listed # and not the average...
thanks for trying

"Miguel Zapico" wrote:

You may use this array formula:
=AVERAGE(LARGE(A1:A52,ROW(1:48)))
Enter it with Crt+Shift+Enter

"LCB" wrote:

I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #'s

Here are some alternatives:

For numbers listed in A1:A52

B1: =(SUM(A1:A52)-SUM(SMALL(A1:A52,{1,2,3,4})))/48

OR
B1: =AVERAGE(LARGE(A1:A52,ROW(1:48)))
Note: for that array formula, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Pro


"LCB" wrote:

I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Miguel Zapico
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #

Maybe it is because of the row formula. You may try this modification:
=AVERAGE(LARGE(A1:A52,ROW(INDIRECT("1:48"))))
Also, check that the formula is entered as an array formula (curly brackets
should appear on the formula bar)

Hope this helps,
Miguel.

"LCB" wrote:

I tried this array: it geave me the highest listed # and not the average...
thanks for trying

"Miguel Zapico" wrote:

You may use this array formula:
=AVERAGE(LARGE(A1:A52,ROW(1:48)))
Enter it with Crt+Shift+Enter

"LCB" wrote:

I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LCB
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #

I believe you have solved my delima. I do not need to use the 4 that are not
computed at all. I do know the range ahead of time..
This is to help me determine the anser to my annual report to the church I
pastor. I must give the average of the highest of 48 Sunday attendences out
of 52 Sundays. THANKS FOR YOUR SHARING YOUR KNOWLEDGE WITH ME. GOD BLESS

"Bernard Liengme" wrote:

This will find the average with the four smallest excluded
=(SUM(A1:A52)-SMALL(A1:A52,1)-SMALL(A1:A52,2)-SMALL(A1:A52,3)-SMALL(A1:A52,4))/48
If there are, for example, 7 ones then the average is computed without 4 of
them.
Not sure what you want to do with duplicates.
Are the numbers integer (whole numbers); do you know the range before hand?
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LCB" wrote in message
...
I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LCB
 
Posts: n/a
Default average of highest 48 of 52 radom numbers with duplicate low #

Thanks, the first formula works as I want it to. the 2nd only returns the
highest number in the list. I appreciated your help... GOD Bless You

"Ron Coderre" wrote:

Here are some alternatives:

For numbers listed in A1:A52

B1: =(SUM(A1:A52)-SUM(SMALL(A1:A52,{1,2,3,4})))/48

OR
B1: =AVERAGE(LARGE(A1:A52,ROW(1:48)))
Note: for that array formula, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Pro


"LCB" wrote:

I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.

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
Average first n numbers in a range (there may be less than n numbe Mike A. M. Excel Discussion (Misc queries) 8 January 18th 06 03:55 PM
Adding 7 highest numbers Stu Excel Discussion (Misc queries) 5 October 11th 05 06:58 PM
AVERAGE Row of Numbers and Return Corresponding Numeric Label Sam via OfficeKB.com Excel Worksheet Functions 14 September 20th 05 01:07 AM
How do you find duplicate values in excel- 2 columns of numbers rickmanz Excel Discussion (Misc queries) 1 December 16th 04 12:16 AM
Add numbers for duplicate entries then delete Chillygoose Excel Worksheet Functions 1 November 2nd 04 05:35 PM


All times are GMT +1. The time now is 11:02 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"