How to Group Numbers at Uneven Intervals in Excel

In this article, we’re going to show you how to group a set of numbers that do not have the same size. For this Excel operation, once again, we’d be using the LOOKUP function.

Group numbers at uneven intervals in Excel

Formula

= LOOKUP (value, intervals, groups)

In explaining this formula, we’ll take a look at an example.

Example

Imagine we’re tasked with the grouping of people based on their ages.

Figure 1. Excel showing the names and ages of people.

A look at the table above shows you a lot of things. First, we have the names of people and their ages.

In the two other columns, we have another “age” column, which represents the intervals. And then, the Grade, which represents the groups. You can take another look at the formula to see this. You’d realize that our formula has the following arguments in it.

• Intervals – refers to the named range from which the lookup values will be returned.
• Groups – refers to the named range where certain values can be found in groups.

Next step

Use the formula to group numbers at uneven intervals here.

Our formula based on this example is: = LOOKUP (B3, E3:E6, F3:F6)

Where:

• Age = E3:E6
• Grade = F3:F6

Entering the formula in the first cell, C3, returns the group that the age in the adjoining cell belongs to.

Figure 2. LOOKUP function groups a number at an uneven interval.

Then, you can perform the same function for each cell to completely group all numbers at uneven intervals.

Notes

• Based on our example, the LOOKUP does a match of the numbers in our column E and returns the corresponding value in the next column F.
• Once the function finds a match in the Age column, it immediately supplies value from the associated group. Should the function not find a match, it will move across the age column until it finds a bigger value, before returning to the previous row.
• Also, according to our example, where the highest age is 67, you can just have “60+” as the last group, instead of the “70” limit we had.
• When arranging your intervals in a column, ensure that you do so in alphabetical order. This way, you won’t have problems grouping numbers at uneven intervals.

Common Errors

• If the age is smaller than even the smallest value in the column, the LOOKUP function returns an error, #N/A.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.