Assign categories to your numeric ranges automatically
We often need to assign a category based on where data falls within a numeric range. You can combine the VLOOKUP function with a named range array to create a formula that translates a numeric value into its correct corresponding category.
To automatically assign an appropriate category to a data value, we’ll:
• Create a sample worksheet with toy safety ratings that assign letter scores depending on the toy’s numeric rating.
• Define a named range that actually refers to an array so our formula knows which category to assign.
• Build a VLOOKUP formula that compares a data value with the named range array and displays the correct category.
There are often easy-to-understand categories associated with numeric values, but it can be a hassle to assign these categories manually. And what if the numeric value changes? You’d need to remember to update the category too. For example, schools often assign grades to numeric ranges — such as a C for scores that fall between 70 and 79. Using our technique, the category updates depending on where the data falls within the numeric scale.
Define your categories
Our sample worksheet, shown in Figure A, is a comparison of toy safety ratings in four different areas — each area has a minimum score of 0 and a maximum score of 25, making the highest possible total 100. The category you assign to each numeric rating is based on where the value falls between 0 and 100. To demonstrate, let’s add a legend of the categories we want to assign to each numeric range.
A:
The Safety Grade column updates when the safety ratings change — if the change pushes the product into a different safety category.
To define the categories and their numeric limits:
1. Launch Excel and open our sample worksheet, or create one based on columns A:F in Figure A.
2. Type the Safety Grade Legend in cells
3. Select the entire Safety Grade Legend and click the dropdown arrow to the right of the Borders button bo
