# Spreadsheet Skills: Dynamic Chart Labels

How do your spreadsheet skills RANK? Do you know how to order numerical data when there are duplicates or blanks? It’s easier than you think. By Liam Bastick, Managing Director (and Excel MVP) with SumProduct Pty Ltd.

## Query

I have a list of numerical data that I need to rank from smallest to largest. I am aware of the RANK function in Excel but it does not seem to cope with duplicate values. Can you advise?

## Advice

For this query, let me construct an example (which is replicated in the attached Excel file):

Here, there are 10 items, with several duplicates and one blank value. That should be plenty to make this example sufficiently awkward!

To order this data, we can use the** RANK** function:

=RANK(number,list,[ranking_order])

where:

**number**is the value to be ranked. It should be noted that the**RANK**value only works with numbers (this is why the last rank in the example below is #N/A – the value is a blank cell; text will create #VALUE! errors);**list**is the range of numbers that are to be ranked. This should be a column or row vector (i.e. the range consists of only one row or only one column); and**ranking_order**is an optional argument. This is why it is in square brackets (do not put the value in square brackets!). If the value is**0**or omitted, the ranking is descending (largest first), whereas if it is 1, the ranking is ascending (smallest first).

The following example employs a simple **RANK** formula:

This example clearly demonstrates the problems:

- As explained above, non-numeric values (e.g. cell
**G22**) create prima facie errors in the results; and - Duplicate values (e.g. rows 20 and 21) give the same ranking. Do not be put off by number formatting: the value in cell
**G14**is actually 7.3 in our example, not 7 similar to the values in cells**G20**and**G21**.

We need to create a formula that removes both of these obstacles. The following is one such calculation when ranking is to be performed in ascending order:

The formula in the first cell in our example is:

=IF($G13="","",RANK($G13,$G$13:$G$22,1)+COUNTIF($G$13:$G13,$G13)-1)

The **IF** condition checks for blank cells and thus avoids #N/A errors. If a more comprehensive error check is required, you may use

=IFERROR(RANK($G13,$G$13:$G$22,1)+COUNTIF($G$13:$G13,$G13)-1,””)

instead.

The **COUNTIF($G$13:$G13,$G13)-1** argument uses the **COUNTIF(List,Criterion)** function, where it counts the number of occurrences of the **Criterion** (value) in the **List** (range). In this instance, the first occurrence adds zero to the ranking, the second a value of one and so on. Note that **List** in this example expands as you move down the range. This deals with duplicates adding a “tie-breaker”. The logic works the same for both ascending and descending ranking.

### Word to the Wise

It does not take much imagination to make this example more sophisticated and this is demonstrated in the attached Excel file. Data validation (see Data Validation) may be used to create a switch between an ascending and a descending sort and **INDEX MATCH** (see Index Match) may be employed to summarise the re-sorted data.

If you have a query for the Spreadsheet Skills section, please feel free to drop Liam a line at liam.bastick@sumproduct.com