Excel Lessons - Grouping Ages Video

MrExcel's Learn Excel #949 - Grouping Ages

This "MrExcel's Learn Excel #949 - Grouping Ages" video requires the latest version of Adobe Flash Player.
Download Flash

Get Flash

Excel Lessons - Grouping Ages -

Steve asks how to group records into age ranges. In Episode 949, I show how to use the range version of...

Excel Lessons - Grouping Ages -

Steve asks how to group records into age ranges. In Episode 949, I show how to use the range version of VLOOKUP to solve this problem.

Added: 2/24/09

Tags:

Excel Lessons - Grouping Ages Grouping Ages Learn Excel Business excel Grouping MrExcel sorting spreadsheets technology VLOOKUP

URL:

Embed: (More)

Comment on this Video

Your Name: Email (optional):

Are you human?

Comments
Add-ons
Links
Transcript

By: Guest 2 months ago

Hello, can you tell me how to, forr example how to find out using VLOOKUP, how may 20 year olds, how many 10 year olds etc. im stuggling to do it this way! Thanks

Reply to this Comment

MrExcel's Learn Excel #949 - Grouping Ages

Hey, welcome back to the Mr. Excel Net Cast. I’m Bill Jelen. Basic, we start out with massive amounts of data and so how we’re going to analyze this, well let’s fire up the table and see if we can solve this problem. Hey! Welcome to the Mr. Excel net cast, I’m Bill Jelen. Question today is from Steve, Steve has some raised results here and you see a column called age. Steve want to group those in the categories like 0-9, 10-19, and so on. Some of the little table over here with the various categories and what I’m going to do is I'm going to label the table with the lowest value in the category and then have a text converts 10-19, 20-29, 30-39, and so on. You get the idea. And then to add the category I want to use a different version of VLOOKUP, most of the time will use comma false at the end of the VLOOKUP which needs an exact match, but we’re going to use the other version of VLOOKUP, the one that has comma true at the end. It says hey go find this age within this table, press “F4” and with the second column but I don’t put comma false at the end we just leave it blank which is same as doing true. And now it doesn’t need an exact match. What it does is finds the value just lower than that so we fill this in here. You see that it feels in the category very easily. Now, Steve wanted then to sort by category and age, so that’s now easy to do. Data sort, sort by category and then we’ll add a new level. I set category age, category on time must be wants to do. And have the fastest times, click okay and now were good. For each category we get to see the times organized fastest. So, relatively good way to do this using the VLOOKUP version that we hardly ever use, that the range of VLOOKUP, where you don’t have to put comma false at the end, you don’t need exact matches. Of course, the one K out here is that this table has to be sorted from lowest to highest, in order work with that range version of the VLOOKUP. Thanks for stopping by, we’ll see you next time for another net cast from Mr. Excel.

No Links were listed yet. Go ahead and share!

Hey, welcome back to the Mr. Excel Net Cast. I’m Bill Jelen. Basic, we start out with massive amounts of data and so how we’re going to analyze this, well let’s fire up the table and see if we can solve this problem.

Hey! Welcome to the Mr. Excel net cast, I’m Bill Jelen. Question today is from Steve, Steve has some raised results here and you see a column called age. Steve want to group those in the categories like 0-9, 10-19, and so on.

Some of the little table over here with the various categories and what I’m going to do is I'm going to label the table with the lowest value in the category and then have a text converts 10-19, 20-29, 30-39, and so on. You get the idea. And then to add the category I want to use a different version of VLOOKUP, most of the time will use comma false at the end of the VLOOKUP which needs an exact match, but we’re going to use the other version of VLOOKUP, the one that has comma true at the end. It says hey go find this age within this table, press “F4” and with the second column but I don’t put comma false at the end we just leave it blank which is same as doing true. And now it doesn’t need an exact match. What it does is finds the value just lower than that so we fill this in here. You see that it feels in the category very easily.

Now, Steve wanted then to sort by category and age, so that’s now easy to do. Data sort, sort by category and then we’ll add a new level. I set category age, category on time must be wants to do. And have the fastest times, click okay and now were good. For each category we get to see the times organized fastest.

So, relatively good way to do this using the VLOOKUP version that we hardly ever use, that the range of VLOOKUP, where you don’t have to put comma false at the end, you don’t need exact matches. Of course, the one K out here is that this table has to be sorted from lowest to highest, in order work with that range version of the VLOOKUP.

Thanks for stopping by, we’ll see you next time for another net cast from Mr. Excel.

Transcription by: Scribe4you Transcription Services