Excel Formula to Sort a List by Rank

🎯 The Excel formula hack that lets you rank text in alphabetical order! This formula trick allows you to sort and rank text in Excel

======================================================================================
🥉 RANK function: Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)

Syntax: RANK(number,ref,[order])

The RANK function syntax has the following arguments:
Number: Required. The number whose rank you want to find.
Ref: Required. An array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.
Order: Optional. A number specifying how to rank number.

If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.
If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.

Remarks
RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).

For some purposes one might want to use a definition of rank that takes ties into account. In the previous example, one would want a revised rank of 5.5 for the number 10. This can be done by adding the following correction factor to the value returned by RANK. This correction factor is appropriate both for the case where rank is computed in descending order (order = 0 or omitted) or ascending order (order = nonzero value).

Correction factor for tied ranks=[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2.

======================================================================================
#Up4Excel
#Up4ExcelFormulas
#Up4ExcelAdvanced

Here at Up4Excel we’re on a mission to help YOU:
» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time

Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.

You get new video releases every week, packed full of ways to save time and impress those around you.

💎 Don't miss out and fall behind.....
🅾 SUBSCRIBE NOW 🅾

======================================================================================
👀 Learn more with my playlist: Excel Formulas and Functions Tips

🎁 Your small gift will help me make better videos for you and others.

👉 All Up4Excel workbooks and templates on YOUR device in a structured searchable index….with free lifetime updates

[UpExcel]

🎯 The Excel formula hack that lets you rank text in alphabetical order! This formula trick allows you to sort and rank text in Excel

[wayneedmondson]

Thanks John. Works well if you have all unique values in your list in column F. If you need to handle duplicates, you can modify this way: =COUNTIFS($F$4:$F$1000, "<="&F4)-(COUNTIFS(F$4:F4, F4)-1). The fun never ends with EXCEL formulas :)) Thumbs up!!

[captainalpha]

Thanks for the video! However, I want an Excel formula that sorts a list of numbers based on which one is the nearest to a reference number. Excel 2016.