+ Reply to Thread
Results 1 to 6 of 6

How to create a single-tailed Gaussian distribution

  1. #1
    Registered User
    Join Date
    01-10-2013
    Location
    Cardiff, CA
    MS-Off Ver
    Excel 16.49 for Mac
    Posts
    15

    How to create a single-tailed Gaussian distribution

    I need to create a data set consisting of 100 data points. The data set needs to be a single-tailed Gaussian distribution (with the tail below the median), with the data points in random order (i.e. not sorted from high to low, or low to high or anything like that).

    The median value of the Gaussian distribution will be 100 and the standard deviation will be 3%. (It would be nice to be able to create the data set with any median and standard deviation.) The data values will be rounded to the nearest integer.

    I found a website that generates data sets with Gaussian distributions after I specify median and standard deviation, and it will create a data set with any number of data points that I want and in random order. But it generates two-tailed data sets - with values above and below the median.

    Two possible solutions to this:

    (1) Can Excel generate a data set per my requirements? (I actually need to generate several such data sets with 100 data points in each data set.)

    (2) I can use the website I found to generate a two-tailed data set with 200 data points, and then import the data set into Excel. Are there steps I could do in Excel to delete the data values above 100 (above the median) and compact the remaining data points into a single column in Excel, preserving the random order?

    I'm using Excel 16.49 on a Mac.

    I posted this question on Reddit and plan to also post it on MrExcel.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: How to create a single-tailed Gaussian distribution

    It's not clear to me how you are generating the data. If I assume they are random numbers:

    Standard Gaussian (normal) random numbers come from something like =norminv(rand(),mean,stdev) where rand() is a random uniform from 0 to 1. As you note, this generates random number from both sides/tails of the distribution. It's not clear to me which tail you want, but I think you control this by controlling the range for the uniform random. If you limit rand() to 0 to 0.5, you will get the left tail of the normal distribution. 0.5 to 1 will generate random numbers from the right tail. You control the range of the random number generator using rand()*(b-a)+a where a is the low end and b is the high end.

    Something like =NORM.INV(RAND()*0.5,100,0.03) will return random numbers from the left side of a normal distribution.
    Something like =NORM.INV(RAND()*0.5+0.5,100,0.03) will return random numbers from the right side of the a normal distribution.

    Does that help?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-10-2013
    Location
    Cardiff, CA
    MS-Off Ver
    Excel 16.49 for Mac
    Posts
    15

    Re: How to create a single-tailed Gaussian distribution

    Thanks for replying so quickly, McShorty. Later tonight I'll try those formulas in Excel and see if they generate what I'm looking for.

  4. #4
    Registered User
    Join Date
    01-10-2013
    Location
    Cardiff, CA
    MS-Off Ver
    Excel 16.49 for Mac
    Posts
    15

    Re: How to create a single-tailed Gaussian distribution

    Thanks again, McShorty. I pasted this formula into 20 Excel cells...

    =NORM.INV(RAND()*0.5,100,0.03)

    ...and got the result in this screen shot....

    Attachment 734275

    (I hope that worked. First time trying to attach an image.)

    The range of values that appeared is 99.9376 to 99.9947.

    The allowable range of values for my project is 0 to 100. I realize now that I should have included that information in my initial description. So, trying to describe it again in a better way:

    I need to generate a data set of 100 random numbers. The range of values is 0 to 100. The median value is 100 and the numbers must be a Gaussian (or normal) distribution that fall on the left (or low) side of the median of 100. The standard deviation of the distribution must be 3%. With a median of 100, the value of the standard deviation will be 3.

    As I typed that, the thought came to me that maybe I need to change 0.03 in the above formula to 3. I'll try that and see what happens.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: How to create a single-tailed Gaussian distribution

    I'm not sure how you plan to return values as small as 0. With a median/mean of 100 and standard deviation of 3, the probability of getting anything below about 80 or 90 is tiny (NORMDIST(80,100,3,TRUE) is about 2E-11). In theory, that formula can return any value between negative infinity and 100, but the probability of getting anything less than 90 is small. If you want to increase the odds of getting smaller numbers, you have to increase the standard deviation.

    In order to get smaller results, what requirement are you able to relax? Can you use a larger standard deviation? A non-Gaussian distribution? Non-uniform random numbers? I'm not sure what to recommend.

  6. #6
    Registered User
    Join Date
    01-10-2013
    Location
    Cardiff, CA
    MS-Off Ver
    Excel 16.49 for Mac
    Posts
    15

    Re: How to create a single-tailed Gaussian distribution

    Good question/comment - thanks.

    I'm creating a mathematical model of a physical system. The simplest way to explain it is that I'm modeling how full a bunch of containers are. There is a set of 100 containers. They start at 100% full, but they all leak at very slow rates, and they all leak at a different rate.

    A container can never be more than 100% full, and it can never be less than 0% full. So the possible range of values is 0 to 100.

    The model we're using to show how full each container is at a given time is a single-tailed Gaussian (or normal) distribution. The median is 100; std dev is 3; and we only want the lower half of the distribution (because they can never be more than 100% full).

    I understand that with a std dev of 3 it's extremely unlikely that we will ever see values below 90, and very very rare to see values below 80.

    So for purposes of this model, maybe the formula you originally proposed (with std dev changed from 0.03 to 3) is good enough. Last night I created 10 datasets with 100 datapoints in each data set, and the data looked good to me.
    Last edited by ClintO; 05-27-2021 at 10:43 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] distribution of values from single cell to multiple cells in single column
    By WhatsGig in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 08-29-2017, 02:30 PM
  2. Two-tailed t-test help please!
    By adiski in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2015, 12:26 PM
  3. Replies: 2
    Last Post: 02-19-2015, 02:00 PM
  4. Create a Distribution List with VBA
    By jeff_kaufman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2013, 11:07 AM
  5. How to create a normal gaussian distribution chart using vba macro?
    By wdn1508 in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 09-09-2009, 10:21 PM
  6. How to create a distribution plot
    By Hertz1976 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2008, 02:48 PM
  7. [SOLVED] How do I get Excel to generate an ex-gaussian distribution?
    By NeedStatsHelp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-23-2006, 02:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1