November 9

A High Ranking Function

Excel really doesn’t have an explicit “between function” to allow users, given a set of ranges, to find in what range a selected number falls. The obvious and messy workaround is to use a bunch of nested IFs, which is tedious and error-prone. That’s where the RANK function comes in.

Here’s a breakdown.

number - The number you want to find
ref - The reference to spreadsheet range
order - optional; excel defaults to descending order

The RANK function returns the location of a specified number in a given range. So let’s say you have a given set, 1,2,3,4,5. Obviously, testing for the number three will return a location of 3. For our range test, we combine a number outside the set and see where it falls.

Check out the image below to see it in action:

Excel returns a rank of 4, since 3.2 will appear in the fourth location of the ranked set, 1,2,3,
At this point, there are two important items to point out.

  • How excel sorts the data for ranking is important. Above we use an ascending order (we pass a 1 to the last argument) and below we’ll use a descending order (we’ll pass a 0 to the last argument). You’ll have to decide which best fits your analysis.
  • Because you combine two ranges into one, you must use parenthesis. If you miss this step, the RANK function will interpret B2 as the next parameter and return an error.

Here’s a Weather Application

This example tests for a temperature’s range and then displays how the day will feel corresponding to that range.

You could also use this trick when you must evaluate a value on a complex function. For example, you might have a graph with several curves that represents some utility for a given value, x. Thus, if you know the bounds of your curves, you can test in what range your x falls and apply its corresponding curve-function.

Finally, you might have noticed that this trick restricts you to inequalities with an inclusive lower bound and exclusive upper bound. There’s ways to fix this and I plan to talk more about them in future posts.

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)


You may also like

February 17, 2013

Are Your Formulas All "Wrapped" Up?
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free 24 Excel Tips Guide

Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!