Need a formula

[Charles Woll]

In columns C to M, where M would be incrementing +1 column each week, find
the average of the highest 7 of 10 numbers. Skipping blank columns.
i.e. Average the of Best 7 of 10.

\\\|///
\\ ~ ~ //
( @ @ )
--oOOo-(_)-oOOo---
Charlie Woll

 

[Bernard Liengme]

Question not too clear but try this
=AVERAGE(LARGE(C1:M1,{1,2,3,4,5,6,7}))

 

[Bob Phillips]

If it is incrementing each week, maybe you want

=AVERAGE(LARGE(1:1,ROW(INDIRECT("1:"&MIN(COUNT(1:1),7)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bill Kuunders]

one way

=(SUM(A1:J1)-SMALL(A1:J1,1)-SMALL(A1:J1,2)-SMALL(A1:J1,3))/(COUNTIF(A1:J1,">0")-3)

 

[Charles Woll]

Sorry, the scores are from a golf league. The spred sheet looks like this:
3/21 9/20 9/27 10/4 10/11 10/18 10/25 11/1 11/8 Points
NAME PHONE Tansi Bear Trace Deer River Run Deer Dorchester
Deer Tansi Deer Need
ALLCORN, LARRY -G 456-1360 13 17
ASHFORD, JIM 707-8423 0 5
BARTH, BOB 456-0906 13 20 26 17 15 13 21 20


There are hidden columns between the dates 3/21/06 and 9/20/05. Want points
needed to be the average of the best 5 scores for the last 7 times the
person played. So in the case of Allcorn, most of the scores are in the
hidden columns. Barth would use scores 13,20,26,17,15,13,and 21 then find
the best 5 which would be 26,21,20,17 and 15. The two 13's are thrown out.
I am doing it now with a custom VB function. But would like to simplify.
Each week, the scores for the week, which in this case are in the colum 3/21
are transferred (inserted) between the last score (11/8) and Points Needed.
Hope this clarifies the problem.
charlie