Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of lowest 5 of 20 values?
The following are what golf handicappers call differential values. I've
listed 25 of them. To compute my golf score handicap I need to take the average of the lowest 5 of the last 20 of these values, and multiply that by 0.96. The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The average of these 5 would be 6.6. Any way I could use my Excel 2003 with some formula? 11.82 13.92 14.96 8.68 17.05 19.15 11.82 13.92 5.55 7.64 14.96 12.87 6.59 11.82 9.73 9.73 6.59 17.05 14.96 6.59 9.73 8.68 7.64 13.92 7.64 Len Miller -- To email reply, eradicate all threes in my SPAM guarded address. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of lowest 5 of 20 values?
One possible way
=SUM(SMALL(A1:A20,{1,2,3,4,5}))/5 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Fatfreek" wrote in message ... The following are what golf handicappers call differential values. I've listed 25 of them. To compute my golf score handicap I need to take the average of the lowest 5 of the last 20 of these values, and multiply that by 0.96. The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The average of these 5 would be 6.6. Any way I could use my Excel 2003 with some formula? 11.82 13.92 14.96 8.68 17.05 19.15 11.82 13.92 5.55 7.64 14.96 12.87 6.59 11.82 9.73 9.73 6.59 17.05 14.96 6.59 9.73 8.68 7.64 13.92 7.64 Len Miller -- To email reply, eradicate all threes in my SPAM guarded address. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of lowest 5 of 20 values?
Hi
Try: =AVERAGE(SMALL(A1:A25,{1,2,3,4,5})) will do the trick HTH Michael M "Fatfreek" wrote: The following are what golf handicappers call differential values. I've listed 25 of them. To compute my golf score handicap I need to take the average of the lowest 5 of the last 20 of these values, and multiply that by 0.96. The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The average of these 5 would be 6.6. Any way I could use my Excel 2003 with some formula? 11.82 13.92 14.96 8.68 17.05 19.15 11.82 13.92 5.55 7.64 14.96 12.87 6.59 11.82 9.73 9.73 6.59 17.05 14.96 6.59 9.73 8.68 7.64 13.92 7.64 Len Miller -- To email reply, eradicate all threes in my SPAM guarded address. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of lowest 5 of 20 values?
Peo,
Thanks very much. That works perfect. Len -- To email reply, eradicate all threes in my SPAM guarded address. "Peo Sjoblom" wrote in message ... One possible way =SUM(SMALL(A1:A20,{1,2,3,4,5}))/5 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Fatfreek" wrote in message ... The following are what golf handicappers call differential values. I've listed 25 of them. To compute my golf score handicap I need to take the average of the lowest 5 of the last 20 of these values, and multiply that by 0.96. The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The average of these 5 would be 6.6. Any way I could use my Excel 2003 with some formula? 11.82 13.92 14.96 8.68 17.05 19.15 11.82 13.92 5.55 7.64 14.96 12.87 6.59 11.82 9.73 9.73 6.59 17.05 14.96 6.59 9.73 8.68 7.64 13.92 7.64 Len Miller -- To email reply, eradicate all threes in my SPAM guarded address. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of lowest 5 of 20 values?
Michael,
I also tried your solution and it works. Thanks. Len -- To email reply, eradicate all threes in my SPAM guarded address. "Michael M" wrote in message ... Hi Try: =AVERAGE(SMALL(A1:A25,{1,2,3,4,5})) will do the trick HTH Michael M "Fatfreek" wrote: The following are what golf handicappers call differential values. I've listed 25 of them. To compute my golf score handicap I need to take the average of the lowest 5 of the last 20 of these values, and multiply that by 0.96. The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The average of these 5 would be 6.6. Any way I could use my Excel 2003 with some formula? 11.82 13.92 14.96 8.68 17.05 19.15 11.82 13.92 5.55 7.64 14.96 12.87 6.59 11.82 9.73 9.73 6.59 17.05 14.96 6.59 9.73 8.68 7.64 13.92 7.64 Len Miller -- To email reply, eradicate all threes in my SPAM guarded address. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of lowest 5 of 20 values?
Thanks for the feedback, glad something met your needs
Regards Michael M "Michael M" wrote: Hi Try: =AVERAGE(SMALL(A1:A25,{1,2,3,4,5})) will do the trick HTH Michael M "Fatfreek" wrote: The following are what golf handicappers call differential values. I've listed 25 of them. To compute my golf score handicap I need to take the average of the lowest 5 of the last 20 of these values, and multiply that by 0.96. The way I see it, the lowest 5 are 5.55, 6.59, 6.59, 6.59, and 7.64. The average of these 5 would be 6.6. Any way I could use my Excel 2003 with some formula? 11.82 13.92 14.96 8.68 17.05 19.15 11.82 13.92 5.55 7.64 14.96 12.87 6.59 11.82 9.73 9.73 6.59 17.05 14.96 6.59 9.73 8.68 7.64 13.92 7.64 Len Miller -- To email reply, eradicate all threes in my SPAM guarded address. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average absolute values | Excel Discussion (Misc queries) | |||
How can I identify the two lowest values in a row? | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
How to average a column, but exclude zero AND negative values? | Excel Discussion (Misc queries) | |||
How do I average a formula without calculating zero values? | Excel Discussion (Misc queries) |