Today's Learn Excel Question: "Can you use Excel to generate Random Numbers that are centered around a mean using a Standard Deviation?" =RANDBETWEEN won't do it, but a clever use of =RAND and =NORM.INV ["Normal Inverse"] will do it. In Episode #1507, Bill shows us how to generate Random Numbers with this method.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1507: Random Using Mean And Standard Deviation.
You know, I have to create lots of fake data sets as I'm writing these books and so I'm a big user of RANDBETWEEN.
I use RANDBETWEEN all the time.
You know, the problem with RANDBETWEEN, though, is that it's going to create just a scattershot of values between, in this case, 1 and a 100, and so, I have a 100 formulas here that are RANDBETWEEN 1 and a 100.
This pivot table summarizes and groups, and, as we refresh the data, you'll see that the…it's just…well, it's random but that's not the way that life really works.
Usually, we have values that are scattered around a mean using some sort of a standard deviation, and so, you know, I want to see if there's a way that I could create random numbers that follow the normal distribution curve, and, of course, starting…here…recently, Excel changed a lot of these functions.
So, we have, you know, NORM.DIST for the distribution but NORM.INVERSE is exactly what we need here.
So, NORM.INVERSE.
Then, we’re looking for a probability between 0 and 1, so, luckily, the RAND function will give me a decimal between 0 and 1, and then the mean and standard deviation.
So, let's say that things are grouped around a mean of 70 with a standard deviation of 10, and so what that should mean is that everything should kind of really be centered between 60 and 80 with a few outliers between 50 and 60 and 80 and 90, and very few falling way outside of that range.
So, let's take this formula and copy it over here to our data, CONTOL+V to paste, and then refresh the pivot table to see what effect we have, alright?
So, yeah.
So, now we're…this is funny.
[ They changed the groupings on me – 02:02 ], but you can see that rather than this whole scattershot between 0 and a 100, we’re definitely focused on the 70 range, and then, further out, let's refresh again to allow it to recalculate.
We're always going to have kind of the value that occurs the most [ in…grouped – 02:22 ] tightly around 70.
[ =NORM.INV(RAND(),70,10) ] So, an interesting variation there, using NORM.INV to generate something between.
I'll give it a mean and a standard deviation.
Kind of a different set of random numbers than you're going to get just using the RAND or RANDBETWEEN functions.
Also avoids having to go out to the Analysis ToolPak.
[ I realize the Analysis TookPak offers us – 02:41 ] but that is just so flaky to use, with all due respect to the people that originally wrote the Analysis ToolPak.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1507: Random Using Mean And Standard Deviation.
You know, I have to create lots of fake data sets as I'm writing these books and so I'm a big user of RANDBETWEEN.
I use RANDBETWEEN all the time.
You know, the problem with RANDBETWEEN, though, is that it's going to create just a scattershot of values between, in this case, 1 and a 100, and so, I have a 100 formulas here that are RANDBETWEEN 1 and a 100.
This pivot table summarizes and groups, and, as we refresh the data, you'll see that the…it's just…well, it's random but that's not the way that life really works.
Usually, we have values that are scattered around a mean using some sort of a standard deviation, and so, you know, I want to see if there's a way that I could create random numbers that follow the normal distribution curve, and, of course, starting…here…recently, Excel changed a lot of these functions.
So, we have, you know, NORM.DIST for the distribution but NORM.INVERSE is exactly what we need here.
So, NORM.INVERSE.
Then, we’re looking for a probability between 0 and 1, so, luckily, the RAND function will give me a decimal between 0 and 1, and then the mean and standard deviation.
So, let's say that things are grouped around a mean of 70 with a standard deviation of 10, and so what that should mean is that everything should kind of really be centered between 60 and 80 with a few outliers between 50 and 60 and 80 and 90, and very few falling way outside of that range.
So, let's take this formula and copy it over here to our data, CONTOL+V to paste, and then refresh the pivot table to see what effect we have, alright?
So, yeah.
So, now we're…this is funny.
[ They changed the groupings on me – 02:02 ], but you can see that rather than this whole scattershot between 0 and a 100, we’re definitely focused on the 70 range, and then, further out, let's refresh again to allow it to recalculate.
We're always going to have kind of the value that occurs the most [ in…grouped – 02:22 ] tightly around 70.
[ =NORM.INV(RAND(),70,10) ] So, an interesting variation there, using NORM.INV to generate something between.
I'll give it a mean and a standard deviation.
Kind of a different set of random numbers than you're going to get just using the RAND or RANDBETWEEN functions.
Also avoids having to go out to the Analysis ToolPak.
[ I realize the Analysis TookPak offers us – 02:41 ] but that is just so flaky to use, with all due respect to the people that originally wrote the Analysis ToolPak.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
