how to make formula

[moko]

I have data Score for games as bellow :


Data

No Name Score Rank

1 Bill 10 5
2 flora 10 5
3 victor 30 3
4 Afti 23 4
5 Dizz 45 1
6 Samuel 37 2



I need to automatic data as below:

Rank Score Name

1 45 Dizz
2 37 Samuel
3 30 victor
4 23 Afti
5 10 Bill
6 10 flora



How to Formula in Name Field?

[John James]

Simplest is to create a pivottable, put all fields in the row section and eliminate totals and subtotals

[Peo Sjoblom]

http://www.cpearson.com/excel/rank.htm

look at player ranking

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"moko" <[email protected]> wrote in message
news:[email protected]...
>
> I have data Score for games as bellow :
>
>
> Data
>
> No Name Score Rank
>
> 1 Bill 10 5
> 2 flora 10 5
> 3 victor 30 3
> 4 Afti 23 4
> 5 Dizz 45 1
> 6 Samuel 37 2
>
>
>
> I need to automatic data as below:
>
> RANK SCORE * NAME*
>
> 1 45 Dizz
> 2 37 Samuel
> 3 30 victor
> 4 23 Afti
> 5 10 Bill
> 6 10 flora
>
>
>
> How to Formula in Name Field?
>
>
> --
> moko
> ------------------------------------------------------------------------
> moko's Profile:
> http://www.excelforum.com/member.php...o&userid=33390
> View this thread: http://www.excelforum.com/showthread...hreadid=534003
>

[Biff]

Hi!

Assume this table is in the range A1:D8. Row 2 is a blank row.

> No Name Score Rank
>
> 1 Bill 10 5
> 2 flora 10 5
> 3 victor 30 3
> 4 Afti 23 4
> 5 Dizz 45 1
> 6 Samuel 37 2

Change your current Rank formula to this:

=RANK(C3,C$3:C$8)+COUNTIF(C$3:C3,C3)-1

Enter that formula in D3 and copy down to D8.

Now, enter these headers somewhere: (assume A10:C10)

Rank, Score, Name

Enter this formula in A12:

=IF(ROWS($1:1)<=COUNT(D$3:D$8),SMALL(D$3:D$8,ROWS($1:1)),"")

Enter this formula in B12:

=IF(A12="","",INDEX(C$3:C$8,MATCH(A12,D$3:D$8,0)))

Enter this formula in C12:

=IF(A12="","",INDEX(B$3:B$8,MATCH(A12,D$3:D$8,0)))

Select all 3 cells, A12, B12 and C12 and copy down until you get blanks.

Biff

"moko" <[email protected]> wrote in message
news:[email protected]...
>
> I have data Score for games as bellow :
>
>
> Data
>
> No Name Score Rank
>
> 1 Bill 10 5
> 2 flora 10 5
> 3 victor 30 3
> 4 Afti 23 4
> 5 Dizz 45 1
> 6 Samuel 37 2
>
>
>
> I need to automatic data as below:
>
> RANK SCORE * NAME*
>
> 1 45 Dizz
> 2 37 Samuel
> 3 30 victor
> 4 23 Afti
> 5 10 Bill
> 6 10 flora
>
>
>
> How to Formula in Name Field?
>
>
> --
> moko
> ------------------------------------------------------------------------
> moko's Profile:
> http://www.excelforum.com/member.php...o&userid=33390
> View this thread: http://www.excelforum.com/showthread...hreadid=534003
>