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.

`RANK(number,ref,[order])number - The number you want to findref - The reference to spreadsheet rangeorder - 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,
3.2,4,5.
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.

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.

Latest posts by Jordan Goldmeier (see all)

Tags

#### You may also like

December 28, 2015

## What Function Helps You Determine Monthly Loan Payments – Excel Challenge

What Function Helps You Determine Monthly Loan Payments – Excel Challenge

November 22, 2015

## “Everyday” Random Shuffling with Formulas

“Everyday” Random Shuffling with Formulas

December 22, 2015

## Using Named Formulas In Excel – Like Yesterday()

Using Named Formulas In Excel – Like Yesterday()

June 25, 2015

## Convert Function In Excel – Video Tutorial

Convert Function In Excel – Video Tutorial

December 4, 2015

## VLookup To The Left With The Choose Function – Excel Tips

VLookup To The Left With The Choose Function – Excel Tips

May 7, 2019

## Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV

Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV 