how to make formula

M

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?
 
J

John James

Simplest is to create a pivottable, put all fields in the row sectio
and eliminate totals and subtotal
 
B

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
Click to expand...

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
 
Top