Wale asks how to randomly choose 20 invoices from a population of 500 invoices. Episode 719 will show you two methods for solving this problem.
This blog is the video 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 blog is the video 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:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today a question sent in by Wale, Wale says hey I have 500 invoices.
I need to choose 20 of those for audit, and he pointed out that he has my book Learn Excel from MrExcel and it's not in that book that's true.
This is kind of an auditing task it's a little bit specialized and so, it's in the other book Excel for Auditor.
So, I'll point you to that book.
I have two ways of doing this in the first way, I guess it's just the quick and dirty way.
I wanna come over here and add a brand new Column and use the function =RAND(), there's no argument.
So, just open and closed parenthesis and I'll double click the fill handle to copy that down.
What it does is, it gives me a random number between zero and one.
Basically, we're going to copy these numbers and then paste values to convert them from random formulas to their values and now, we can simply sort.
So, I can either choose ascending or descending and the first 20 records are the ones we're going to audit or if you sort descending, same basic concept which ever ones happen to come up first or last are the ones to audit.
So, that's the quick way, the other way is to use the Analysis Toolpak.
Of course we have to make sure to turn this on under tools and then add-ins, make sure Analysis Toolpak is turned on its always installed, but not turned on by default and then we have a whole bunch of new options Tools, Data Analysis within Data Analysis lots of different programs we can run, but here we're going to use Sampling, will click OK.
Input Range is the range of invoices from A2 to A501, we can choose Periodic sampling which would say give me every 17th record or in this case random sample just asked for 20 samples and we're incentive to a new workbook, click OK.
And there we have it our 20 invoices.
Note that this works with just a single column.
So, we'd then have to use VLOOKUP to go back and get the information for those invoices, but either way would work.
I wanna thank Wale for sending that question in and thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today a question sent in by Wale, Wale says hey I have 500 invoices.
I need to choose 20 of those for audit, and he pointed out that he has my book Learn Excel from MrExcel and it's not in that book that's true.
This is kind of an auditing task it's a little bit specialized and so, it's in the other book Excel for Auditor.
So, I'll point you to that book.
I have two ways of doing this in the first way, I guess it's just the quick and dirty way.
I wanna come over here and add a brand new Column and use the function =RAND(), there's no argument.
So, just open and closed parenthesis and I'll double click the fill handle to copy that down.
What it does is, it gives me a random number between zero and one.
Basically, we're going to copy these numbers and then paste values to convert them from random formulas to their values and now, we can simply sort.
So, I can either choose ascending or descending and the first 20 records are the ones we're going to audit or if you sort descending, same basic concept which ever ones happen to come up first or last are the ones to audit.
So, that's the quick way, the other way is to use the Analysis Toolpak.
Of course we have to make sure to turn this on under tools and then add-ins, make sure Analysis Toolpak is turned on its always installed, but not turned on by default and then we have a whole bunch of new options Tools, Data Analysis within Data Analysis lots of different programs we can run, but here we're going to use Sampling, will click OK.
Input Range is the range of invoices from A2 to A501, we can choose Periodic sampling which would say give me every 17th record or in this case random sample just asked for 20 samples and we're incentive to a new workbook, click OK.
And there we have it our 20 invoices.
Note that this works with just a single column.
So, we'd then have to use VLOOKUP to go back and get the information for those invoices, but either way would work.
I wanna thank Wale for sending that question in and thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
