=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))
That formula is correct.
Test it on these 20 values:
82,88,84, 87,80,81, 86,79,77, 82,88,90, 84,75,80,80,80,84,83,83
Assume those values are in the range G1:G20
Extract the 10 smallest:
A1:A10 = 1,2,3,4,5,6,7,8,9,10
B1 = formula copied down to B10:
=SMALL(G:G,A1)
Now: =AVERAGE(B2:B9) = 80.11111
Your formula:
=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))
= 80.11111
Biff
"Bill Foley" <pttincatitexasdotnet wrote in message
...
Hey Gang,
Excel 2003
I have created (with help from my friends here) a little spreadsheet that
tracks my handicap at my golf club using the array below. I created the
worksheet function that does the following:
Averages the lowest 10 of the last 20 scores, factors in the slope and .96
fudge factor and comes up with a handicap. Problem at our course is they
use their own system and throws in a twist. They still take the lowest 10
of the last 20, but they drop the lowest of those 10 scores and use the
remaining 9 to figure a handicap.
Here is the current function that I have been using for the lowest 10 of
the last 20. Any way to drop the lowest of those 10 and only take the
remaining 9 with the formula below?
{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("1:10"))))}
I tried using:
{=AVERAGE(SMALL(OFFSET(G1,COUNT(G:G)-1,,-20),ROW(INDIRECT("2:10"))))}
but the number just didn't seem consistent. Maybe it is and I just
paranoid that my handicap is too dang low! HA!
TIA!
--
Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com