Easy Excel Formula to Categorize Numbers by Range

Sharing is caring!

Are you looking for an Excel formula to categorize numbers by range? Categorizing and grouping numbers based on ranges is a common task in Excel, whether you’re analyzing sales data, student grades, survey responses, or other numerical information. In this article, we’ll show you step-by-step how to use Excel formulas to automatically categorize numbers into ranges and groups. You’ll learn several methods, from basic IF formulas to more advanced techniques using VLOOKUP and nested IFs.

Why Categorize Numbers into Ranges in Excel?

Before we dive into the formulas, let’s discuss why you might need to group numbers into categories or ranges in Excel:

• Data Analysis: Categorizing numbers allows you to quickly analyze and summarize large datasets by grouping values into meaningful ranges.
• Reporting: Presenting data in categories makes it easier to understand and communicate insights to stakeholders.
• Charting: Grouping numbers into ranges is often necessary for creating charts like histograms or pivot charts that display the distribution of values.
• Conditional Formatting: You can apply different formatting to cells based on the category they fall into, making it easy to visually identify patterns and outliers.

Now that you understand the importance of categorizing numbers, let’s look at how to do it using Excel formulas.

Using IF Formula to Categorize Numbers

The simplest way to categorize a number into a range is using the IF formula. The IF formula tests a condition and returns one value if the condition is true, and another value if false.

Here’s the basic syntax of the IF formula:

=IF(logical_test, value_if_true, value_if_false)

To categorize numbers, you can use the logical_test argument to check if a number falls within a certain range.

For example, let’s say you want to categorize test scores into three groups: “Low” (Less than 60), “Medium” (60-80), and “High” (Greater than 80).

Assume the test scores are in column A, starting from cell A2. In cell B2, enter the following formula and drag it down:

=IF(A2<60, "Low", IF(A2<=80, "Medium", "High"))

The formula first checks if the value in A2 is less than 60. If true, it returns “Low”. If false, it moves to the next IF statement and checks if the value is less than or equal to 80. If true, it returns “Medium”. If both conditions are false, the formula returns “High”.

Here’s what the result looks like:

The IF formula works well if you have only a few categories. But what if you need to categorize numbers into many ranges? In that case, it’s better to use a lookup formula like VLOOKUP.

Using VLOOKUP to Categorize Numbers into Ranges

VLOOKUP is an Excel function that looks for a value in a table and returns a corresponding value from another column. It’s a powerful way to map numbers to categories based on ranges.

To use VLOOKUP for categorizing numbers, you first need to create a lookup table that defines the ranges and their corresponding categories.

For example, let’s say you want to group ages into these ranges:

• 0-17: Minor
• 18-64: Adult
• 65+: Senior

Create a lookup table like this:

The VLOOKUP formula to categorize the ages (assuming they are in column A starting from A2) is:

=VLOOKUP(A2, F2:H4, 3, TRUE)

Here’s how the VLOOKUP arguments work:

• lookup_value (A2): The age value you want to look up and categorize.
• table_array (F2:H4): The lookup table range that includes the age ranges and categories.
• col_index_num (3): The column number of the lookup table range that contains the return value (category). In this case, the category is in the 3rd column of the specified table array.
• range_lookup (TRUE): Use TRUE to find an approximate match. This allows VLOOKUP to match a value to a range, rather than requiring an exact match.

Drag the VLOOKUP formula down to categorize the entire list of ages. The result looks like this:

VLOOKUP is convenient because you can easily modify the lookup table to adjust the ranges without having to rewrite the formula.

Combining IF and VLOOKUP for More Complex Categorization

In some cases, you may need to perform more complex categorization based on multiple conditions. You can combine the IF and VLOOKUP formulas to handle such scenarios.

For instance, suppose you want to categorize sales amounts into Small, Medium, and Large, but the range for each category depends on the region. Here’s the criteria:

East Region:

• Less than $1000: Small
• $1000-$4999: Medium
• $5000 or more: Large

West Region:

• Less than $500: Small
• $500-$1999: Medium
• $2000 or more: Large

To solve this, create two lookup tables – one for each region. Then use an IF formula to check the region, and nested VLOOKUPs to find the appropriate category from the corresponding lookup table.

Assume the data is structured like this:

• Region in column A
• Sales Amount in column B

East Region Lookup Table:

West Region Lookup Table:

Enter this formula in C2 and drag it down:

=IF(A2="East", VLOOKUP(B2, $G$2:$I$4, 3, TRUE), 
 IF(A2="West", VLOOKUP(B2, $K$2:$M$4, 3, TRUE), 
 "Invalid Region"))

The formula first checks if the region is “East”. If true, it looks up the sales amount in the East lookup table to find the category. If false, it checks if the region is “West” and looks up the category in the West table. If the region is neither East nor West, it returns “Invalid Region”.

Here’s a sample result:

By combining IF and VLOOKUP, you can categorize numbers based on different ranges for each group or condition.

Tips for Using Excel Formulas to Categorize Numbers

Here are some tips to keep in mind when using formulas to group numbers into ranges:

1. Double-check your ranges: Make sure the ranges in your formulas or lookup tables cover all possible values and don’t overlap. A number should only fall into one category.
2. Use absolute cell references: When creating lookup tables, use absolute cell references (with $) for the table range in your formulas. This allows you to drag the formula without messing up the lookup range.
3. Handle blank or non-numeric values: If your data might contain blank cells or text values, wrap your formulas in an IFERROR or IFNA function to handle those cases gracefully and avoid formula errors.
4. Validate results: After applying categorization formulas, spot-check the results to make sure the formulas are working as intended. You can use Excel’s data validation feature to restrict input values and prevent accidental formula overwrite.
5. Consider helper columns: If your categorization logic is complex, it might be helpful to break it down into multiple steps using helper columns. This can make your formulas more readable and easier to troubleshoot.

Final Thoughts

Categorizing numbers into ranges is a valuable skill for organizing and analyzing data in Excel. Whether you use simple IF formulas, VLOOKUP with a lookup table, or a combination of functions, Excel provides flexible tools to group numbers based on any criteria you need. By mastering these formulas, you’ll be able to slice and dice your numerical data in meaningful ways, uncovering insights and trends that might be hidden in a sea of raw numbers.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.