Grouping Dates with Pivot Tables

I am not a gambling man. But if I were, I’d bet a million dollars that you will never ever grasp all that Microsoft Excel has to offer.  Fine with me because that means we can learn something new just about every day.  Pivot Tables provide a perfect example.

Pivot Table Basics

I’v raved about Pivot Tables in the past , praising the power and flexibility of this Excel feature.  In literally seconds, you can slice and dice data any way you wish to see just the data you need to make informed decisions.  (See prior post on Pivot Tables)

The journey between just learning about Pivot Tables and being able to effectively implement and use them is indeed a short one.  But take just a few more steps and you’ll find additional ways to save time, while adding helpful details to your information and analysis.  Let’s say, for example your company keeps records for each sales transaction.  A simplified table might include the name of the salesperson along with the date and amount of the transaction.

Using just the basic elements of Pivot Tables, one can easily produce a table showing the total sales for each date and each salesperson. (See Illustration).  But having data laid out in this manner is not completely helpful.  A better way to group the information might be by year, quarter, or month.  Or all three.  There are, of course, many ways to add those levels of grouping when producing this pivot table.  Those new to Pivot Tables might add columns to the raw data, converting the date to columns for the month (using the month() function), year (using the year() function), and quarter (applying the roundup() function to a simple equation: monthNumber/3- see :What Quarter is It?).  Even adding one for the week wouldn’t be that difficult. Read more of this post

Floor and Ceiling Functions

While the ROUNDUP (round up to the nearest number, specifying the number of digits), ROUNDDOWN (round down to the nearest number, specifying the number of digits), and ROUND (round to nearest number up or down, specifying the number of digits) are very helpful, sometimes you wish to round up or down to a specific multiple.  Maybe you are dealing with large numbers and want to deal work only with multiples of 1,000 or 10,000.  Or maybe you’re building a graph or chart and want to minimize the scale.  The best way to do that, you decide, is to make the base (lower bound value) lower than, but as close to, the lowest number as possible.

No matter the reason, performing this is easy.  For each of them, you only need the number you are rounding and the multiple you’d like to round (up or down) to.  Examples follow:

Value: A1=1,798

Floor(A1,5)=1,795
Floor(A1,10)=1,790
Floor(A1,25)=1,775
Floor(A1,100)=1,700
Floor(A1,1,000)=1,000
Floor(A1,1,500)=1,500
Floor(A1,2,000)=0

Ceiling(A1,5)=1,800
Ceiling(A1,10)=1,800
Ceiling(A1,25)=1,800
Ceiling(A1,100)=1,800
Ceiling(A1,1,000)=2,000
Ceiling(A1,1,500)=3,000
Floor(A1,2,000)=0

As always, if you have questions, drop me a line or post a comment.