Trying to rank a list of scores this way. So it will show the ties.
Then I need to add the top 4 and sum together.
number ranking
2 1
3 2
3 2
5 3
6 4
so on
First problem However using the rank formula does not give this result I've
tried
something like this
=SUM(INDEX(LARGE(F6:F29,ROW(1:4)),0))

this gives me
number rank
2 1
3 2
3 3
5 4
6 5

Then not sure how to grab tthe top four I tried

Rob,

This isn't clear. The 'normal' rank formula for those numbers

=RANK(A1,$A$1:$A$5,1) returns this

2 1
3 2
3 2
5 4
6 5

Using the array formula

=SUMPRODUCT(--(A1$A$1:$A$5),1/COUNTIF($A$1:$A$5,$A$1:$A$5&""))+1

returns what I think you want which is

2 1
3 2
3 2
5 3
6 4

I know don't understand what you want to sum. If it's the top 4 of the
original data then why bother with rank at all why not simply sum the top 4

=SUM(LARGE(A1:A5, {1,2,3,4}))

or using the array formula

=SUM(LARGE(A1:A5,ROW(1:4)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike





"Rob" wrote:

Trying to rank a list of scores this way. So it will show the ties.
Then I need to add the top 4 and sum together.
number ranking
2 1
3 2
3 2
5 3
6 4
so on
First problem However using the rank formula does not give this result I've
tried
something like this
=SUM(INDEX(LARGE(F6:F29,ROW(1:4)),0))

this gives me
number rank
2 1
3 2
3 3
5 4
6 5

Then not sure how to grab tthe top four I tried

Hello,

Maybe this helps:
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

Great Help

Second question with the
=SUM(LARGE(A1:A5, {1,2,3,4}))
Can I use conditonal format to hightlight the values that are used?
I am using this to add the top four scores in a list and would like to
hightlight them?

Thanks Rob



"Mike H" wrote:

Rob,

This isn't clear. The 'normal' rank formula for those numbers

=RANK(A1,$A$1:$A$5,1) returns this

2 1
3 2
3 2
5 4
6 5

Using the array formula

=SUMPRODUCT(--(A1$A$1:$A$5),1/COUNTIF($A$1:$A$5,$A$1:$A$5&""))+1

returns what I think you want which is

2 1
3 2
3 2
5 3
6 4

I know don't understand what you want to sum. If it's the top 4 of the
original data then why bother with rank at all why not simply sum the top 4

=SUM(LARGE(A1:A5, {1,2,3,4}))

or using the array formula

=SUM(LARGE(A1:A5,ROW(1:4)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike





"Rob" wrote:

Trying to rank a list of scores this way. So it will show the ties.
Then I need to add the top 4 and sum together.
number ranking
2 1
3 2
3 2
5 3
6 4
so on
First problem However using the rank formula does not give this result I've
tried
something like this
=SUM(INDEX(LARGE(F6:F29,ROW(1:4)),0))

this gives me
number rank
2 1
3 2
3 3
5 4
6 5

Then not sure how to grab tthe top four I tried