Breaking rank—differentiating between values that are equal

Challenge: On a dashboard report, show the top five values from a table of data showing the number of respondents. And against these values, provide other data from the same records.

At first glance, the above challenge seems rather simple. The LARGE function brings back the nth largest value in a range, and VLOOKUP can then be used to bring back the associated data from that record.

But because the number of respondents is a discrete number (i.e. it can only take integer values), then there may be a tie. And VLOOKUP doesn't like ties.

The way around this is to add a small, static random number to each of the numbers of respondents in the source table. And to use this as the basis for looking up.

Let's assume the number of respondents start in cell A2. Create two new columns to its right. In cell B2, enter the following formula:

=RAND()*0.0001

The RAND function creates a random number between 0 and 1. The multiplication by 0.0001 makes that number tiny, but—and importantly—positive. Also, given the level of accuracy to which random numbers are stored in Excel, they're most likely unique.

Copy over the formulae in column B with values. This will hard-code the random numbers and avoid them recalculating and jumping around.

Now in cell C2, type the following formula:

=A2+B2

By adding the small random number to the original number of respondents, you've created a number negligibly higher than the number of respondents. And by formatting that new column as an integer with no decimal places, it will appear identical to column A. But importantly, all of the numbers in column C are unique.

Now in the dashboard, you can use the LARGE function against column C to bring back the five largest values. And when you use VLOOKUP against that same column, it will be able to differentiate between the rows and bring back unique values.

• Tweet