Explanation

In this example, the goal is to group ages into buckets. One way to do this is to prepare a table with age breakpoints in the first column, and the name of the appropriate group or bucket in the second column. Then use a lookup function to find the right bucket or group for each age. In the example shown, we are using the VLOOKUP function for this job, and the formula in cell F7, copied down, is:

=VLOOKUP(D5,age_table,2,TRUE)

The inputs to VLOOKUP are as follows:

  • lookup_value - the age from cell D5
  • lookup_table - “age_table”, which is the named range G5:H8.
  • col_index_num - 2 to indicate “2nd column”
  • range_lookup - TRUE to specify an approximate match.

Note that because the last argument, range_lookup , is optional and defaults to TRUE, it is not necessary to provide a value for this problem. However, I personally like to set this value explicitly as a reminder of the match behavior that is expected. Also note that we are using a named range for convenience only, since named ranges do not change when copied. If you prefer, you can use an absolute reference like $G$5:$H$8 instead. We don’t want this reference to change as the formula is copied down.

In the configuration above, VLOOKUP looks up each age in the table and returns the appropriate group from the second column. Because VLOOKUP is set to perform an approximate match, it has a particular behavior. It will scan through the ages and stop at the first exact match, or, when no exact match is found, it will stop at the first larger value and then “step back” to the previous row and return the group from that row. The groups in the second column can be any values you wish.

For more details on VLOOKUP, see How to use the VLOOKUP function .

The age table must be sorted in ascending order by age for this formula to work properly.

Pivot tables

Pivot tables can automatically group numbers , but the VLOOKUP approach allows you to perform completely custom grouping.

Explanation

If you need to group times into buckets (i.e. group by 6 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR.

In the example shown, we have a number of transactions, each with a timestamp. Let’s say you want to group these transactions into buckets of 3 hours like this:

12:00 AM-3:00 AM 3:00 AM-6:00 AM 6:00 AM-9:00 AM 9:00 AM-12:00 PM

For example, a time of 2:30 AM, needs to go into the 12:00 AM - 3:00 AM bucket. A time of 8:45 AM needs to go into the 6:00 AM-9:00 AM bucket, and so on.

If you think about it, one way to do this is to round each time until it fits into the right bucket. However, unlike normal rounding, where we might round to the nearest multiple, in this case, we want to round down to the nearest multiple , starting at midnight.

Because Excel times are just decimal numbers , you can easily do this with the FLOOR function , which rounds down to a multiple that you supply (FLOOR calls the argument that represents multiple “significance”). Even better, FLOOR understands how to round time provided in a format like “h:mm” (for example, “3:00”, “12:00”, etc.).

In the example shown, the formula in E5 is:

=FLOOR(D5,"3:00")

FLOOR knows how to read time, so it interprets 3:00 as its decimal equivalent, 0.125. It then simple rounds down each time to the nearest multiple of 0.125 You can use this same approach to group times into any standard bucket that you like.

If you have times that span one or more days, you can use the MOD function to extract just the time, as explained here .

Pivot tables

Pivot tables will automatically group times into buckets of 1 hour, but they can’t automatically group into other time buckets. However, using the approach outlined on this page, you can group time as you like, then run the resulting data through a pivot table to summarize.