MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Varied Bucket Sizes

June 19, 2017 - by Bill Jelen

Varied Bucket Sizes

Pivot tables are great for creating frequency distributions. But the built-in functionality assumes that each bin or bucket is an identical size. What if you have uneven bins?

Watch Video

  • Creating Stratifications with a Pivot Table
  • Yesterday, in episode 2101, used automatic grouping
  • That forces all buckets to be the same size - $10K in yesterday's example
  • To create buckets of varying sizes, there are two options:
  • Select a manual group and choose Group
  • Use a Lookup table to apply a category and add that to the pivot table

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2102 varied bucket sizes hey welcome
  • back to MrExcel net cast I'm Bill
  • gelling yesterday in episode 20 101 I
  • showed how you could do group to take
  • these two great stratifications and
  • Excel dot I Adam pointed out that you
  • could have different bucket sizes i
  • limited yesterday that all these bucket
  • sizes are ten thousand that's how I set
  • it up so let's take a look at how to do
  • that to set it up manually choose one
  • cell here we'll go to analyze and
  • ungroup and then out what Adam is
  • suggesting is to take all the items that
  • are should be in the first bucket so I'm
  • going to go up to ten thousand
  • everything up to ten thousand like this
  • and then instead of group field I use
  • group selection and it makes all of
  • those be group one and then we'll come
  • out over here find the next set so from
  • ten thousand up to twenty thousand like
  • that and then group selection okay and
  • then we'll go from twenty thousand up to
  • thirty thousand and group that selection
  • and then finally we'll take everything
  • all the million-dollar orders just group
  • them into a single selection and at this
  • point we can get rid of the revenue
  • column just leaving us these four and
  • you have to remember let's see under 10k
  • 10k 20k 20k to 30k and then over 1mm I'm
  • using mm for a million there right so
  • yeah that absolutely works although it's
  • a bit of a hassle to go through it as
  • Adam said so what I might do instead is
  • come back to the original data set and
  • build a little lookup table over here
  • and we'll just create a simple little
  • formula equals vlookup that revenue
  • amount into this table the table has to
  • be sorted because it's going to be one
  • of those weird lookups where I'm going
  • to do comma true at the end for exact
  • match I usually recommend against that
  • but at this particular case is one of
  • those rare times
  • or work all right so we will insert
  • pivot table make sure that we're going
  • out to the new column J the size click
  • OK take sighs put size down on the left
  • hand side and then to see the number of
  • orders go with customer and then we can
  • look at profit or whatever just as we
  • did yesterday so profit and this one
  • could be a percentage running total I
  • like that all right so a couple of
  • different ways to create stratifications
  • between yesterday's episode 20 101 and
  • today's episode twenty one or two okay
  • now all of those topics have the manual
  • grouping and the lookup table are all
  • covered in this book power excel at mr.
  • excel click that I on the top right hand
  • corner okay episode recap we're creating
  • stratifications with a pivot table again
  • yesterday in episode 20 101 we used
  • automatic grouping but that forces all
  • buckets to be the same size yesterday
  • ten thousand for each bucket three
  • buckets of varied sizes there are two
  • options select a manual group and choose
  • group or use a lookup table to apply a
  • category and add that to the pivot table
  • okay I want to thank Adam for that I
  • don't want thank you for stopping by
  • we'll see you next time for another net
  • cast from MrExcel

Download File

Download the sample file here: Podcast2102.xlsm

Title Photo: Photoshot / Pixabay

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.