Excel - Random Around Mean and Standard Deviation: Episode 1507
28.2 هزار بار بازدید -
13 سال پیش
-
Microsoft Excel Tutorial: Generating Random
Microsoft Excel Tutorial: Generating Random Numbers Around a Mean and Standard Deviation in Excel | MrExcel Podcast.
Welcome to another episode of the MrExcel podcast. In this episode, we will be discussing how to generate random numbers around a mean and standard deviation in Excel. As someone who frequently creates fake data sets for my books, I am a big user of the RANDBETWEEN function. However, this function only generates random values between a given range, which is not always reflective of real-life data.
To solve this issue, I discovered the NORM.INVERSE function in Excel, which allows us to generate random numbers that follow a normal distribution curve. This means that the majority of values will be centered around a mean, with a few outliers on either side. To use this function, we simply need to provide a probability between 0 and 1, a mean, and a standard deviation.
For example, let's say we want to generate random numbers around a mean of 70 with a standard deviation of 10. We can use the formula =NORM.INV(RAND(),70,10) to achieve this. As we refresh the data and update our pivot table, we can see that the values are now more tightly grouped around 70, with fewer extreme outliers. This is a much more realistic representation of data compared to using the RANDBETWEEN function.
Using the NORM.INVERSE function also eliminates the need to use the Analysis ToolPak, which can be unreliable at times. So, if you're looking for a more accurate way to generate random numbers in Excel, give the NORM.INVERSE function a try. Thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe and stay tuned for more helpful tips and tricks for mastering Excel. See you next time!
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-on-youtube/
Table of Contents:
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Random Using Mean and Standard Deviation
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Need for Fake Data Sets and Use of RANDBETWEEN
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Limitations of RANDBETWEEN
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Introduction to NORM.INVERSE
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Using NORM.INVERSE to Generate Random Numbers
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Example with Mean and Standard Deviation
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Refreshing Pivot Table to See Results
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Alternative to Analysis ToolPak
(https://www.seevid.ir/fa/w/IcNOaPCYqEY) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Analysis ToolPak
Learn Excel
Mean and standard deviation
Netcast from MrExcel
NORM.INVERSE
Normal distribution curve
Pivot table
Podcast Episode 1507
RANDBETWEEN
Random Using Mean And Standard Deviation
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153185/
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!
13 سال پیش
در تاریخ 1390/10/22 منتشر شده
است.
28,298
بـار بازدید شده