How do you set up a ranking system on Excel?

get ranking
hoosierdaddy81 asked:

It’s a simple problem, with most likely a complicated answer. I have a series of data that is in auto-sum, I want the greatest value to be ranked first and the lesser value, which may be negative, to be last. The ranks go from 1st to 17th.
This will be a contiguos series, I don’t want to keep resorting every time I enter something.



Comments (5)    Posted in Software    

Subscribe RSS 2.0 feed. Leave a response, or Trackback from your own site.




5 Responses to 'How do you set up a ranking system on Excel?'

  1. alecsstan - September 23rd, 2009 at 10:12 am

    select the range you want to sort.
    select “sort…” .. in descending order

  2. Kumar - September 26th, 2009 at 9:35 am

    Near the formula bar there is a sign for function, click on it and enter rank and it will help you with the “rank function”

    format is rank(number, range or array, ascending or descending)

  3. cozmosis - September 29th, 2009 at 11:34 am

    Consider using the RANK funtion to return the rank of a value within a range or use the LARGE function to retun the value of a given rank in a range.

  4. VBAXLMan - September 29th, 2009 at 8:37 pm

    Here is an Auto-Sort sample file

    and lesson1

    and lesson2

    Enjoy them, they got exactly what you need

    VBAXLMan

  5. brayden - October 1st, 2009 at 12:26 am

    I did it using VBA code. My column to search is Column A. Do you know how to input the VBA code? If not, just right click over “sheet 1″, click “view code” and then paste this code below in. Now anytime you enter numbers in column A it will automatically sort as soon as you leave the cell.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim colAValue As Variant
    Dim formulaStr As String
    If Not Intersect(Target, [A:A]) Is Nothing Then
    colAValue = Target.EntireRow.Range(“A1″).Value
    formulaStr = Application.Max(Cells(Rows.Count, 1).End(xlUp).Row, Cells(Rows.Count, 2).End(xlUp))

    Cells.Sort Key1:=[A1], Order1:=xlAscending

    If TypeName(colAValue) = “String” Or colAValue = vbNullString Then
    colAValue = Chr(34) & colAValue + Chr(34)
    End If

    formulaStr = “=max(–(A1:A” & formulaStr & “=” & colAValue & “)*row(A1:A” & formulaStr & “))”
    Cells(Evaluate(formulaStr), 3).Select
    End If
    End Sub


Leave a Reply