software

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.You may use this array formula:
=AVERAGE(LARGE(A1:A52,ROW(1:48)))
Enter it with Crt Shift Enter

quot;LCBquot; wrote:

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

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

quot;LCBquot; gt; wrote in message
...
gt;I must determine out of 52 radom numbers the average of the 48 highest even
gt; if one or more of the lowest numbers is repeated 7 times without exempting
gt; the 3 out of the 7 that must be included in the 48 to extract the average.
gt;
I tried this array: it geave me the highest listed # and not the average...
thanks for trying

quot;Miguel Zapicoquot; wrote:

gt; You may use this array formula:
gt; =AVERAGE(LARGE(A1:A52,ROW(1:48)))
gt; Enter it with Crt Shift Enter
gt;
gt; quot;LCBquot; wrote:
gt;
gt; gt; I must determine out of 52 radom numbers the average of the 48 highest even
gt; gt; if one or more of the lowest numbers is repeated 7 times without exempting
gt; gt; the 3 out of the 7 that must be included in the 48 to extract the average.
gt; gt;

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-Proquot;LCBquot; wrote:

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

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

Hope this helps,
Miguel.

quot;LCBquot; wrote:

gt; I tried this array: it geave me the highest listed # and not the average...
gt; thanks for trying
gt;
gt; quot;Miguel Zapicoquot; wrote:
gt;
gt; gt; You may use this array formula:
gt; gt; =AVERAGE(LARGE(A1:A52,ROW(1:48)))
gt; gt; Enter it with Crt Shift Enter
gt; gt;
gt; gt; quot;LCBquot; wrote:
gt; gt;
gt; gt; gt; I must determine out of 52 radom numbers the average of the 48 highest even
gt; gt; gt; if one or more of the lowest numbers is repeated 7 times without exempting
gt; gt; gt; the 3 out of the 7 that must be included in the 48 to extract the average.
gt; gt; gt;

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

quot;Bernard Liengmequot; wrote:

gt; This will find the average with the four smallest excluded
gt; =(SUM(A1:A52)-SMALL(A1:A52,1)-SMALL(A1:A52,2)-SMALL(A1:A52,3)-SMALL(A1:A52,4))/48
gt; If there are, for example, 7 ones then the average is computed without 4 of
gt; them.
gt; Not sure what you want to do with duplicates.
gt; Are the numbers integer (whole numbers); do you know the range before hand?
gt; best wishes
gt;
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;LCBquot; gt; wrote in message
gt; ...
gt; gt;I must determine out of 52 radom numbers the average of the 48 highest even
gt; gt; if one or more of the lowest numbers is repeated 7 times without exempting
gt; gt; the 3 out of the 7 that must be included in the 48 to extract the average.
gt; gt;
gt;
gt;
gt;

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

quot;Ron Coderrequot; wrote:

gt; Here are some alternatives:
gt;
gt; For numbers listed in A1:A52
gt;
gt; B1: =(SUM(A1:A52)-SUM(SMALL(A1:A52,{1,2,3,4})))/48
gt;
gt; OR
gt; B1: =AVERAGE(LARGE(A1:A52,ROW(1:48)))
gt; Note: for that array formula, hold down [Ctrl] and [Shift] when you press
gt; [Enter].
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;LCBquot; wrote:
gt;
gt; gt; I must determine out of 52 radom numbers the average of the 48 highest even
gt; gt; if one or more of the lowest numbers is repeated 7 times without exempting
gt; gt; the 3 out of the 7 that must be included in the 48 to extract the average.
gt; gt;