A big car company offered a boat load of money to put an annoying ad in this podcast for the next two months. Rather than sell out, a reminder that the podcast is sponsored by the MrExcel.com store. Then; a fast way to group age data using a pivot table. Episode #950 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically we start out with massive amounts of data.
How we're gonna analyze as well let's fire up a pivot table.
Lets see if you can solve this problem.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen. I got an interesting email not an excel question no but an offer this was an offer from a car company let's just say it's a planet that's sixth from the sun offered me a huge boatload of money to put a 20 second commercial in the MrExcel podcast every day for the next two months, a boatload of money, and I thought about that.
I said it's only a one and a half to two minute podcast.
We already have an annoying theme taking out 15 seconds at the beginning and in the end.
I don't want their money so good news for the next two months, you're not going to have this annoying commercial.
You're just having this one commercial. Help me out. Come here to MrExcel.
Take a look at the Mr. Excel store.
We have DVDs we have books you can get the live lessons DVDs, which are my whole power excel seminar for 39 bucks cheaper than going to a seminar, cheaper than taking a day off for the podcast DVD, Rather than me selling this podcast to someone else.
Let's just remember to stop by the MrExcel store every once in a while and contributing what would be able to keep doing this for free.
Now yesterday Steve sent in a question, where he wanted to group this data by age, age ranges and we used VLOOKUP.
Now this wouldn't solve Steve's problem, but it's related and I want to talk about how we could do this with a pivot table.
Let's say that steve wanted to figure out the average time by each age group. All right.
We can do that in either version of excel the old version or the new version using a pivot table and we'll click ok what I'm going to do is I'm going to put the ages down the left hand side in the row labels area and then the times in the heart of the pivot table, and I want to change it from a count to an average.
Click OK, and I get to see the average time for each individual item.
Now I should have while i was there gone into, number Format, and format it as an actual time.
Good, okay now.
To very quickly group those items up here's what we're going to do. We're going to go to the first age right click and choose group.
Now because this is a numeric field and not a date field, we get this version of the grouping dialog box on let's say, I want to group from 0 to 70 in 10 year increments and so what I get automatically is 0-9, 10-19, 20-29 and so on just a great way to figure out statistics for each age group without having to do a VLLOKUP like we did yesterday.
Hey there you have it. Want to thank Steve for sending in yesterday's question and which also led to this podcast.
Thanks to the car company, but no thanks, will let them keep their money and remember to stop by the MrExcel store. Check out our books CDS, and so on Thanks for stopping by.
Have a great weekend. See you next time for another netcast from MrExcel.
Basically we start out with massive amounts of data.
How we're gonna analyze as well let's fire up a pivot table.
Lets see if you can solve this problem.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen. I got an interesting email not an excel question no but an offer this was an offer from a car company let's just say it's a planet that's sixth from the sun offered me a huge boatload of money to put a 20 second commercial in the MrExcel podcast every day for the next two months, a boatload of money, and I thought about that.
I said it's only a one and a half to two minute podcast.
We already have an annoying theme taking out 15 seconds at the beginning and in the end.
I don't want their money so good news for the next two months, you're not going to have this annoying commercial.
You're just having this one commercial. Help me out. Come here to MrExcel.
Take a look at the Mr. Excel store.
We have DVDs we have books you can get the live lessons DVDs, which are my whole power excel seminar for 39 bucks cheaper than going to a seminar, cheaper than taking a day off for the podcast DVD, Rather than me selling this podcast to someone else.
Let's just remember to stop by the MrExcel store every once in a while and contributing what would be able to keep doing this for free.
Now yesterday Steve sent in a question, where he wanted to group this data by age, age ranges and we used VLOOKUP.
Now this wouldn't solve Steve's problem, but it's related and I want to talk about how we could do this with a pivot table.
Let's say that steve wanted to figure out the average time by each age group. All right.
We can do that in either version of excel the old version or the new version using a pivot table and we'll click ok what I'm going to do is I'm going to put the ages down the left hand side in the row labels area and then the times in the heart of the pivot table, and I want to change it from a count to an average.
Click OK, and I get to see the average time for each individual item.
Now I should have while i was there gone into, number Format, and format it as an actual time.
Good, okay now.
To very quickly group those items up here's what we're going to do. We're going to go to the first age right click and choose group.
Now because this is a numeric field and not a date field, we get this version of the grouping dialog box on let's say, I want to group from 0 to 70 in 10 year increments and so what I get automatically is 0-9, 10-19, 20-29 and so on just a great way to figure out statistics for each age group without having to do a VLLOKUP like we did yesterday.
Hey there you have it. Want to thank Steve for sending in yesterday's question and which also led to this podcast.
Thanks to the car company, but no thanks, will let them keep their money and remember to stop by the MrExcel store. Check out our books CDS, and so on Thanks for stopping by.
Have a great weekend. See you next time for another netcast from MrExcel.
