Go Back

How to Group Numbers at Uneven Intervals in Excel

There are times when we are required to group numbers into intervals of unequal sizes. To do this, we need to understand how to use the LOOKUP function in excel. This article provides a clear guide on how to group numbers at uneven intervals in excel.

Figure 1: How to group numbers at uneven intervals

General syntax of the formula

=LOOKUP(value, intervals, groups)

Explanation of the formula

The LOOKUP function plays a fundamental role in helping us group at uneven intervals. In the example above, the groups are in column C.

The formula in cells C2 is =LOOKUP(B2,D5:D8,E5:E8)

Understanding how the formula works

  • The function is configured in a way that allows it to lookup values in the ranges supplied. In our example above, the function is configured as follows;
  • The lookup values are ages found in column B.
  • The lookup vector is the named range “age”, D5:D8
  • Result vector is names as “group” which is E5:E8
  • The above setup allows the LOOKUP function to perform an appropriate match in the numeric values in the column D. it then returns the values associated to it from column E.

Things to note

  • LOOKUP function will return a corresponding group is it finds an exact match in the age column,
  • In the event that the function does not find an exact match, it will traverse the age column until it finds a larger value.
  • The function will return the largest value which is associated with the 50+
  • The LOOKUP function returns an #N/A error in the event age is less than the smallest value in the age column.
  • For this function to work appropriately, the ages must appear in an ascending order.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
I need to build an Excel model which tests NPV at 1% intervals from 1% to 40%
An Excelchat Expert solved this problem in 25 mins!
I am trying to count the number of people that work at some point within two hour intervals. It doesn't matter whether they work for 30 minutes of the interval or throughout the whole thing. I've tried multiple times but the numbers are wrong a lot.
An Excelchat Expert solved this problem in 25 mins!
I have a time column (12:00 AM-11:30 PM it is at 1/2 intervals), I have start and end times for Supervisors. I need to find a formula that will help me find how many Supervisors work at all of the different intervals throughout the day. This would also include overnight. Please and thank you!
An Excelchat Expert solved this problem in 16 mins!
Here are some problems that our users have asked and received explanations on
i I need to build a formula that will make the calculations of compound interest for a credit line that has drawdowns at uneven intervals in uneven amounts...
Solved by E. E. in 12 mins
I want to plot a line graph of 5 data points on the x axis that are not at regular intervals eg (15,34,59,83,98), I want the data points to be independant of the x-axis labelling, so therefore i can change the x-axis labels to be round numbers at constant intervals eg (0,20,40,60,80,100)
Solved by M. S. in 24 mins
Hello, I am working with a large temperature data set which has data points at certain intervals apart. The time intervals change throughout the data set and I was wondering if there was any way I could bin the values so that they are averaged to every hour as opposed to the random intervals which they are at now? The Date Time data has a (M/D/YY H:M) setup
Solved by E. C. in 11 mins

Leave a Comment

avatar