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.
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.
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.