Excel Yourself: No rankling in the ranks

Excel Yourself

There is an effective way of getting automated top-down rankings, even where duplicated values are involved.

Question
Is there a way to list the top 5 values using a formula, where there may be duplicates? 

Answer
You can combine a few functions to arrive at a solution to this issue. Of course, you could sort the list, but if you need a separate report of the top 5 values then formulas can automate the process.

Excel has a RANK function which I have covered in a previous article. Its limitation is that it duplicates the same rank number if there are two or more equal values. You can see this in column C of Figure 1 where the sales value on rows 4 and 7 are the same.

The RANK function in cell C2 is: =RANK(B2,$B$2:$B$11)

This formula has been copied down to the other cells in the column. 

Figure 1

Figure 1

 Syntax

RANK(Value,Range)

Value – the value to be ranked. Usually a relative reference (no $ symbols), in our example it is cell B2.

Range – the range that holds all of the values to be ranked. This range tends to be a fixed reference. In our example $B$2:$B$11 is a fixed range. This enables the formula to be copied to the other cells that need to be ranked.

Client C and Client F have the same sales and the RANK function has returned 2 for both as they have tied for the second highest value. The problem is that there is no rank of 3, because the next highest rank value in column C is 4.

We need the sequential values from 1 to 5. To calculate a rank that has sequential numbers, we can use another function that includes a special range technique. The formula in cell D2, which has been copied down, is:
=RANK(B2,$B$2:$B$11)+COUNTIF(B2:$B$11,B2)-1

The RANK function is identical to the previous one. The COUNTIF function is used to adjust the RANK function result to achieve the sequential numbers.

COUNTIF performs a conditional count. It will count how many times an entry appears in a range. One is subtracted at the end of the formula because the value will always appear at least once in the range.

Syntax

COUNTIF(Range, Value)
Range – the range containing all of the values. In our example B2:$B$11 – see discussion below about the type of range used.

Value – the value to count within the range. Usually a cell reference, B2 in our example.
The secret with the above COUNTIF formula is the use of a fixed reference for the end of the range $B$11, but a relative reference for the start of the range, B2.

This means that as the formula is copied down the column, the range gets smaller because the range’s starting cell is aligned to the formula cell.

The outcome of the formula in cell D4 is that the RANK function will have the result of 2 as per column C. The COUNTIF function will return 2 because the duplicated sales figures are in the range B4:$B$11. So the formula will return 2 + 2 - 1 = 3, the result in D4.

The COUNTIF range’s starting cell is the same row as the formula cell.

The outcome of the formula in cell D7 is that the RANK function will have the result of 2 as per column C. The COUNTIF function will return 1 because the sales figure only appears once in the range B7:$B$11. So the formula will return 2 + 1 - 1 = 2, the result in D7.

This technique adjusts the RANK result to achieve the required sequential values.

Having achieved the sequential numbers, you can then use the INDEX and MATCH functions in combination to extract the top 5 entries. I have also covered these two functions in previous articles. See Figure 2

The cells in column F only contain entries. The formula in cell G2 is: =INDEX(A:A,MATCH($F2,$D:$D,0))

This extracts the name of the top-ranked client. The formula can be copied across and down to complete the table in G2:H6 and also extract the sales values.

The INDEX function allows you to extract an entry from a range.

Figure 2

Figure 2

Syntax

INDEX(Range, Row_number, Column_number)
Range – can be a range; a single row or a single column. Our example uses the single column A:A.

Row_number – the row number within the range of the cell to extract. In our case this is supplied by the MATCH function. Using a MATCH function adds flexibility to the INDEX function.

Column_number – (optional) the column number within the range of the cell to extract. In our example this is not required.
The MATCH function returns a number representing the position of an entry within a range.

SYNTAX

MATCH(Value,Range,Search_Type)
Value – the value to find within the range. Cell $F2, holds the value 1. The $ sign fixes the column reference so the formula can be copied across.

Range – the range containing all the values. In our case, we are searching column $D:$D where we have created the sequential rank numbers. The reference is fixed so the formula can be copied across.

Search_Type – in our case, we have used 0 which means an exact match is required. An approximate match is possible if the range is sorted. In our case, the range isn’t sorted, so we have to use an exact match.
In our example, the MATCH function finds the position of the number 1 (the value in cell F2) within column D. This will return the value 9 because 1 is in the ninth cell in column D. When the MATCH is used on a whole column it returns the row number of the entry being looked for.

The INDEX range is the whole of column A and the MATCH function returns the row number 9 to extract from column A, which will extract the value from cell A9.
This final report is dynamic. Any changes in columns A and B will adjust the top 5 report in columns G and H. Column C is not required, it was only used to demonstrate the RANK function.
 
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.
 
This article is from the April 2014 issue of INTHEBLACK magazine.


How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel