Varied Bucket Sizes

Learn Excel from MrExcel, Podcast Episode 2102: Varied Bucket Sizes.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  Yesterday in Episode 2101, I showed how you could do a group to take these, to create Stratifications and Excel.i Adam pointed out that you could have different bucket sizes.  I lamented yesterday that all these bucket sizes are 10,000, 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 what Adam is suggesting is to take all of the items that should be in the first bucket.  So I'm going to go up to 10,000, everything up to 10,000 like this.  And then instead of Group Field, I use Group Selection and it makes all of those be Group 1.  And then we'll come out over here, find the next set.  So from 10,000 up to 20,000, like that and then Group Selection.  Okay, and then we'll go from 20,000 up to 30,000 and group that selection.  And then finally, we'll take everything all the million dollar orders and 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 to $20k, $20k to $30k and then over $1MM.  I'm using MM for a million there.  Alright, 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 =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 ,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.

Alright, so we will Insert Pivot Table, make sure that we're going out to the new column J.  At the size, click OK, take Size, put Size down along the left-hand side.  And then to see the number of orders, go with our 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, like that.  Alright, so a couple of different ways to create Stratifications between yesterday's Episode 2101 and today's Episode 2102.

Okay now, all of those topics: the Manual Grouping and the Lookup Table are all covered in this book, Power Excel with MrExcel.  Click that “i” on the top right-hand corner.

Okay, episode recap:  We're creating Stratifications with a Pivot Table again.  Yesterday in Episode 2101, we used automatic grouping but that forces all buckets to be the same size, yesterday 10,000 for each bucket.  To create 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 idea.  I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.