which function to use

[slyda]

Here's the situation:

I've got a list of names in a column and each one has up to 15 test
scores - one score per row. What function do I use if I want to sum
the best 11 test scores?

Some cells are blank and should count as zero if that matters.

Sorry to ask such an elementary question but I've looked for this
function for 45 minutes now and can't find it.

Thanks for any help you can provide!

 

[Tom Ogilvy]

=sum(large(B:B,{1,2,3,4,5,6,7,8,9,10,11}))

or do you mean the best 11 scores per person.

 

[slyda]

Sorry - yes, I meant the best 11 scores for each individual.

 

[Peo Sjoblom]

One way, assuming the individuals are in A and the scores in B

=SUM(LARGE(IF(A2:A30="individual",B2:B30),ROW(INDIRECT("1:11"))))

entered with ctrl + shift & enter

replace individual with the name or with a cell where you type the name

 

[Ken Wright]

Can you elaborate on how your data is laid out, only I'm not quite clear on
that. It may well be exactly as I'm reading, in which case it looks like the
following:-

A
Joe
2
3
4
7
4
5
Fred
4
1
2
5
6
Bill
2
3
4
etc

but it could be that it looks like this:-

A B C
Joe Fred Bill
2 4 2
3 1 3
4 2 4
7 5
4 6
5

etc

 

[slyda]

Thanks. I had to transpose the formula a little since my data are
"horizontal" i.e. records (names) are in a column and test scores are
across in rows like this:

name | test1 | test2 | test3
J.B. Weld | 89 | 93 | 78
Husa Berg | 91 | 85 | 89
etc.


You're right though - it's clumsy - sure takes up a lot of space - but
it works! I thought there would be a function for picking the top X
number of values in a block since it seems like that would be a fairly
common need but I guess not.

Thanks again for your help!

Steve Lyda

 

[Peo Sjoblom]

You already got an answer how to do that with one formula


=SUM(LARGE(IF(A2:A30="individual",B2:B30),ROW(INDIRECT("1:11"))))

if the data is horizontal you can use

=SUM(LARGE(IF(A2:Z2="individual",A1:Z1),ROW(INDIRECT("1:11"))))

both entered with ctrl + shift & enter