Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average first n numbers in a range (there may be less than n numbe | Excel Discussion (Misc queries) | |||
Adding 7 highest numbers | Excel Discussion (Misc queries) | |||
AVERAGE Row of Numbers and Return Corresponding Numeric Label | Excel Worksheet Functions | |||
How do you find duplicate values in excel- 2 columns of numbers | Excel Discussion (Misc queries) | |||
Add numbers for duplicate entries then delete | Excel Worksheet Functions |