which function to use

  • Thread starter Thread starter slyda
  • Start date Start date
S

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!
 
=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.
 
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
 
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

This is a bit clumsy but it works.

Assuming your scores are in some of the cells between B7:B21
In C7 put

=IF(B7="",0,B7)

In D7 put

=LARGE($C$7:$C$21,E7)

Starting at C7 and numbering down put 1, 2, 3, etc

Drag or copy the formulas in C7 and D7 to fill all the cells down to C21 and
D21.

In another cell put the formula

=SUM(D7:D17)

which is the sum of the top 11 scores.

Alan
 
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
 
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
 
Back
Top