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.
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.
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.
Here's a breakdown.
RANK(number,ref,[order])
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,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.
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.
