Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create Column Chart in Excel
  • Working With Tables in Excel
  • Get column name from index in Excel Table
  • Subtotal function in Excel
  • How to Create Thermometer Chart in Excel

References

  • To count total rows in a range in Excel
  • How to use Excel VLOOKUP Function
  • Perform case-sensitive Lookup in Excel
  • Multi-criteria lookup and transpose in Excel
  • How to use Excel INDIRECT Function

Data Validations

  • Excel Data validation allow uppercase only
  • Data validation must not exist in list
  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only

Group times into unequal buckets in Excel

by

This tutorial shows how to Group times into unequal buckets in Excel using the example below;

Formula

=VLOOKUP(time,bucket_table,column,TRUE)

Explanation

This formula is a great example of how you can use VLOOKUP to group data in completely custom ways.

If you need to group times into buckets, but the buckets are not equal (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can do so with the VLOOKUP function set to approximate match.

The problem

There are several ways to group times in Excel. If you just need to group times by the hour, a pivot table is very quick and easy. If you need to group times into other equal buckets of multiple hours (i.e. 3 hours, 4 hours, etc.) a nice solution is to use the FLOOR function. However, if you need to group times into unequal buckets, you need to take a more custom approach. VLOOKUP, in its approximate match mode, allows you to group times into custom intervals of any size.

The solution

The solution is to build a lookup table that “maps” each time into the right bucket. In the first column, enter the start time for the bucket. In column two, enter the name of the bucket you want to use. The table must be sorted by the start time, smallest to largest.

Finally, configure VLOOKUP to look up each time in the bucket table with approximate match.

In the example shown, the formula n E5 is:

=VLOOKUP(D5,buckets,2,1)

D5 is the lookup value, “buckets” is a named range for G5:H8, 2 is the column index, and 1 is a flag that enables approximate match. (You can also use TRUE).

When VLOOKUP is in approximate match mode, it matches the nearest value that is less than or equal to the lookup value. In this way, you can think of the incoming lookup time as being “rounded down” into the right bucket.

Post navigation

Previous Post:

VLOOKUP with two client rates in Excel

Next Post:

How to Create Gantt Chart in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRUE Function
  • Extract multiple matches into separate rows in Excel

Date Time

  • Calculate days remaining in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Count holidays between two dates in Excel
  • Basic timesheet formula with breaks in Excel
  • How to calculate next scheduled event in Excel

Grouping

  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • Running count group by n size in Excel

General

  • How to calculate total from percentage in Excel
  • Common Errors in Excel
  • How to calculate percent of goal in Excel
  • Automatically fill series of cells in Excel using AutoFill
  • Basic numeric sort formula in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning