With a helper column, we can use a combination of the RANK and COUNTIF Functions to dynamically sort data that contains only numeric values. The steps below will walk through the process.
Figure 1: Basic Numeric Sort Formula
Syntax
=COUNTIF(range,criteria)
- COUNTIF count cells with numbers, dates and certain text that match specific criteria in a range.
=RANK(number,ref[order]) RANK function is used to determine the position of a value in an array.
Formula
=RANK(B4,Cost)+COUNTIF($B$4:B4,B4)-1
Setting up the Data
- We will set up the data by inputting the items into Column A
- We will input the cost of each item into Column B
- Column C is where we want the formula to return the result of the sorted data
- Note- We must highlight Cell B4 to B11 and name it as Cost by clicking on B3 where there is a drop-down arrow in figure 2
Figure 2: Setting up the Data
Numerical Sorting of the Data
- We will click on Cell C4
- We will insert the formula below into the cell
=RANK(B4,Cost)+COUNTIF($B$4:B4,B4)-1 We will press the enter key
Figure 3: Result for Cell B4
- We will click on Cell C4 again
- We will double-click on the fill handle (the small plus sign at the bottom right of Cell C4) and drag down to copy the formula into the other cells
Figure 4: Results of basic numeric sort formula
Explanation
=RANK(B4,Cost)+COUNTIF($B$4:B4,B4)-1
RANK uses the named range (Cost) to generate values from the highest to lowest starting with 1.
COUNTIF is required if there are duplicate values. A mixed reference is used to enter the range so that the formula continuously expands when copied to the other cells. We have to subtract 1 to zero out this effect. The formula, therefore, returns zero until a duplicate value is found and returns 1 at the second instance.
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment