< Go Back

Group numbers at uneven intervals

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.

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, which are as different as 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 an 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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar