Excel Challenge - Grouping by Uneven Bucket Sizes - Episode 2102
6.6 هزار بار بازدید -
7 سال پیش
-
Microsoft Excel Tutorial: Creating Stratifications
Microsoft Excel Tutorial: 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
Welcome back to the MrExcel netcast! In this episode, we will be discussing varied bucket sizes in Excel. In our previous episode, we showed how to create stratifications using automatic grouping. However, one of our viewers, Adam, pointed out that it is possible to have different bucket sizes. So, in today's episode, we will explore how to manually set up varied bucket sizes in Excel.
To begin, we will select a cell and go to the Analyze tab. From there, we will ungroup the data and then select all the items that should be in the first bucket. In this example, we will select everything up to 10,000 and then use the "Group Selection" option to make all of those items part of Group 1. We will repeat this process for the remaining buckets, selecting the appropriate range and using the "Group Selection" option. Finally, we will group all the million-dollar orders into a single selection.
While this method works, it can be a bit of a hassle to go through each selection manually. As an alternative, we can create a lookup table and use the VLOOKUP function to categorize the data. This method involves sorting the lookup table and using the "TRUE" option for an exact match. We can then insert a pivot table and use the lookup table to categorize the data into different bucket sizes.
All of these topics, including manual grouping and using a lookup table, are covered in detail in our book, "Power Excel with MrExcel." You can find a link to the book in the top right-hand corner of this video. In summary, to create varied bucket sizes in Excel, you can either manually group the data or use a lookup table to categorize it. Thank you to Adam for the idea and thank you for watching. We'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
#excel
#microsoft
#microsoftexcel
#exceltutorial
#walkthrough
#evergreen
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
#excelnavigation
This video answers these common search terms:
how to bucket numbers in excel
how to group by price buckets in excel
how to add buckets or groups in excel
how to bucketize in excel
how to create data buckets in excel
how to do buckets in excel
how to do stratified sampling in excel
how to stratify a sample in excel
how to do a bucket analysis on excel
how to group pivot excel
how to create groups in pivot table in excel
how to group numbers in excel pivot table
how do you group numbers on excel pivot table
how do you group fields in an excel pivot table
how to create buckets in excel
how to group data into buckets in excel
how to make buckets for data in excel
how to make buckets in excel
how to make categorical variables for number buckets excel
how to set up aging buckets in excel
how to use group selection in a pivot table in excel
Table of Contents:
(00:00) Varied Bucket Sizes in Excel
(00:10) Automatic Grouping in Episode 2101
(00:20) Method 1: Manual with Group Selection
(01:03) Using a Lookup Table for Varied Bucket Sizes
(02:03) Recap of Options for Varied Bucket Sizes
(03:02) Clicking Like really helps the algorithm
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
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
Welcome back to the MrExcel netcast! In this episode, we will be discussing varied bucket sizes in Excel. In our previous episode, we showed how to create stratifications using automatic grouping. However, one of our viewers, Adam, pointed out that it is possible to have different bucket sizes. So, in today's episode, we will explore how to manually set up varied bucket sizes in Excel.
To begin, we will select a cell and go to the Analyze tab. From there, we will ungroup the data and then select all the items that should be in the first bucket. In this example, we will select everything up to 10,000 and then use the "Group Selection" option to make all of those items part of Group 1. We will repeat this process for the remaining buckets, selecting the appropriate range and using the "Group Selection" option. Finally, we will group all the million-dollar orders into a single selection.
While this method works, it can be a bit of a hassle to go through each selection manually. As an alternative, we can create a lookup table and use the VLOOKUP function to categorize the data. This method involves sorting the lookup table and using the "TRUE" option for an exact match. We can then insert a pivot table and use the lookup table to categorize the data into different bucket sizes.
All of these topics, including manual grouping and using a lookup table, are covered in detail in our book, "Power Excel with MrExcel." You can find a link to the book in the top right-hand corner of this video. In summary, to create varied bucket sizes in Excel, you can either manually group the data or use a lookup table to categorize it. Thank you to Adam for the idea and thank you for watching. We'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
#excel
#microsoft
#microsoftexcel
#exceltutorial
#walkthrough
#evergreen
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
#excelnavigation
This video answers these common search terms:
how to bucket numbers in excel
how to group by price buckets in excel
how to add buckets or groups in excel
how to bucketize in excel
how to create data buckets in excel
how to do buckets in excel
how to do stratified sampling in excel
how to stratify a sample in excel
how to do a bucket analysis on excel
how to group pivot excel
how to create groups in pivot table in excel
how to group numbers in excel pivot table
how do you group numbers on excel pivot table
how do you group fields in an excel pivot table
how to create buckets in excel
how to group data into buckets in excel
how to make buckets for data in excel
how to make buckets in excel
how to make categorical variables for number buckets excel
how to set up aging buckets in excel
how to use group selection in a pivot table in excel
Table of Contents:
(00:00) Varied Bucket Sizes in Excel
(00:10) Automatic Grouping in Episode 2101
(00:20) Method 1: Manual with Group Selection
(01:03) Using a Lookup Table for Varied Bucket Sizes
(02:03) Recap of Options for Varied Bucket Sizes
(03:02) Clicking Like really helps the algorithm
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
7 سال پیش
در تاریخ 1396/03/29 منتشر شده
است.
6,646
بـار بازدید شده