ranking

[numberonetwin]

what formula would be used to rank scores if you had the following



name score
participant 1 14
participant 2 27
Participant 3 19


to get this result

Participant 2 27
Participant 3 19
Participant 1 14

 

[Datsmart]

If you want a formula solution do this:

Excel Workbook
	A	B	C
1	name	score
2	participant 1	14	3
3	participant 2	27	1
4	Participant 3	19	2
Sheet

Looks like you just sorted the range by the score column descending for your result.

 

[numberonetwin]

is there a formula that would rank the names to correspond with the order of ranking from highest to lowest as well?

 

[numberonetwin]

that formula works great though for ranking without order...thanks

 

[Datsmart]

A formula can not change the arrangement of your data on the page.

If you want to SORT the data:
Select the data,
Click "Data" in the standard menu bar
Click SORT
Under "Sort by", choose the column you want to use, (score)
Choose "Descending"
OK

 

[numberonetwin]

so in other words, you have to manually sort the information after each data input...

thanks for your help!

 

[Datsmart]

You can automate the SORT with VBA code.
Ideally, if you use code to add new data, you could add a sort routine to that code.
This code will SORT data that is congruent to cell A2.

    Range("A2").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Column B is used as SORT criteria.

 

[numberonetwin]

I think i will just stick with the manual! don't know much about vba

thanks!

 

[Datsmart]

To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macros start with "Sub MacroName()" and End with "End Sub"

Sub SortData()
    Range("A2").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess _ 
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal
End Sub

How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialog box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.

 

[Aladin Akyurek]

Maybe...

Book3
	A	B	C	D	E
1	name	score
2	participant 1	14		participant 2	27
3	participant 2	27		Participant 3	19
4	Participant 3	19		participant 1	14
5
6
Sheet1

D2:

Control+shift+enter...

=IF(ROWS($D$3:D3)<=ROWS($A$2:$A$4),INDEX($A$2:$A$4,SMALL(IF($B$2:$B$4=E2,ROW($B$2:$B$4)-ROW($B$2)+1),COUNTIF($E$2:E2,E2))),"")

then copy down.

E2, copy down:

=LARGE($B$2:$B$4,ROWS($E$3:E3))