Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Two-column Lookup in Excel
  • Count rows with at least n matching values
  • To count total rows in a range in Excel
  • How to use Excel MATCH Function
  • How to get relative row numbers in a range in Excel

Data Analysis Examples

  • Conditional Formatting New Rule with Formulas in Excel
  • Randomize/ Shuffle List in Excel
  • How to add Trendline to a chart in Excel
  • Create Scatter Chart in Excel
  • How To Sort One Column or Multiple Columns in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation must begin with
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells
  • Excel Data validation require unique number

Basic numeric sort formula in Excel

by

To dynamically sort data that contains only numeric values, you can use a helper column and a formula created with the RANK and COUNTIF functions.

Formula

=RANK(A1,values)+COUNTIF(exp_rng,A1)-1

Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here.

Explanation

In the example shown, the formula in D5 is:

=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1

where “sales” is the named range C5:C11

How this formula works

The core of this formula is the RANK function, which is used to generate a rank of sales values, where the highest number is ranked #1:

Worked Example:   Excel Data validation exists in list
=RANK(C5,sales)

Here, RANK uses the named range “sales” (C5:C11) for convenience. By default, RANK will assign 1 to the highest value, 2 to the second highest value, and so on. This works perfectly as long as numeric values are unique. However, to handle numeric values which contain duplicates, we need to use the COUNTIF function to break ties. This is done by adding the result of this snippet to the value returned by RANK:

COUNTIF($C$5:C5,C5)-1

Notice the range is entered as a mixed reference that will expand as the formula is copied down the table. As written, this reference will include the current row, so we subtract 1 to “zero out” the first occurrence. This means the expression will return zero for each numeric value until a duplicate is encountered. At the second instance, the expression will return 1, at the third instance, it will return 2, and so on. This effectively breaks ties, and allows the formula to generate a sequential list of numbers with no gaps.

Worked Example:   Break ties with helper column and COUNTIF in Excel

Once the formula is in place, data can be sorted by the helper column. It can also be retrieved with INDEX using the values in the helper column.

Worked Example:   Find duplicate values in two columns in Excel

Post navigation

Previous Post:

Basic text sort formula in Excel

Next Post:

How to set check register balance in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract text between parentheses in Excel
  • How to count line breaks in cell in Excel worksheet
  • Find function vs Search function in Excel
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • How to Check If A Cell Contains Specific Text in Excel
  • Dynamic date list in Excel
  • Next biweekly payday from date in Excel
  • How to Calculate Age in Excel
  • Get date from day number in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment periods for loan in Excel
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles