Sorting Data in a Row Question

K

Karto

I run a Cub Scout pack & know very little about excel & need help with a
project I am working on.

Basically, it's laid out like this:
Names:..player 1, player 2, player 3, player 4, so on
Total:....(total).......(total)......(total).....(total) , etc
Place:....4th............2nd..........1st..........3rd
round 1
round 2
round 3
round 4
so on

The cell that says (total) contains "=sum(row#:row#)" so basically it shows
each players total after each round.

I need help using the cells containing the totals to figured who has the
most points, second most, 3rd most, etc & then display something in the
"Place" row to designate which player is in 1st, 2nd, 3rd, & so on.

Is this something that can be done simply?
 
X

xlmate

try using the RANK function

in cell B3, place this formula

=RANK(B2,$B$2:$E$2)

Pls note that if there are 2 total of the same, this formula will rank them
as equal.

this formula will give an average ranking if there are 2 totals of the same
number
this is an array formula and must be confirm by pressing Ctrl,Shift and
Enter together
=SUM(1*(B2<=$B$2:$E$2))-(SUM(1*(B2=$B$2:$E$2))-1)/2

choose one of this that suit your need and adjust the range to yours

HTH

--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis
 
K

Karto

Max -

Thank you sooooo much... after looking up some examples, & playing around
with it, I was able to make this work exactly as I wanted.

Rod
 
Y

Yoyo

I couldn't get 'xlmate''s formula to work, so I experimented a bit.

You can use RANK and COUNT to give tied players the *average*
of the ranks they occupy (e.g. if the 4th and 5th players tie, they
are both ranked 4.5; and if the 7th, 8th and 9th players tie, they
are all ranked 8th).

In cell B3, place the formula:
=(COUNT($B$2:$E$2)+1+RANK(B2,$B$2:$E$2,0)-RANK(B2,$B$2:$E$2,1))/2

Then select that cell, copy it, and paste it (or just its formula) to
the remaining cells in the row, i.e. C2:E2.

The results are the average ranks in descending order. If, for
any reason, you wanted to rank the players from the bottom up,
you would just reverse the signs of the two RANK() functions.

Hope this, too, helps!

Please provide your feedback by clicking the Yes button below
if this post has helped you. This will help others to search the
archives more easily for useful results.

Regards
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top