Generating a skewed normal distribution of random numbers

[Graham.Johnson]

Hi,

I need to randomly generate a set of 5000 numbers using just the following three inputs:
>Min
>Mean
>Max
They have to be normally distributed, but as the mean is never exactly half way between the min and max, the distribution will be skewed.

I am using =(NORMSINV(RAND())*0.13)+0.5 to give me 5000 random numbers that are normally distributed with a mean of 0.5 and (almost always) have a min of 0 and a max of 1.

I am then mapping these to the distribution that I require using
If random number < 0.5, f(x)=min+2*rand*(mean-min)
If random number = 0.5, f(x)=mean
If random number > 0.5, f(x)=mean+2*(rand-0.5)*(max-mean)

As expected this is giving me numbers within the predefined bounds, however the mean is way off what I was expecting.

For example, a min of 50,000, mean of 225,000 and a max of 1,000,000 give the following output:
>Min ~ 50,000-60,000 (this is close enough)
>Mean ~ 288,000-290,000 (this is no where near)
>Max ~ 950,000-1,000,000 (this is close enough)

I am not sure if there is a problem with my mapping formula, if it is to do with how I am generating the random numbers in the first place, or something else, but I am now stuck.

If anyone has any experience of this, or can offer any advice, then I would be most grateful!

Many thanks in advance,

Graham

 

[Graham.Johnson]

Update:
So, I've worked out that because of the skew of the distribution, the "mean" that I have been specifying is actually the mode. When I grouped the random numbers generated in the example and grouped them, the mode is 225,000 or there abouts.

So, all I now need to do, I think(!), is generate a formula that will take my desired mean, and convert it into a mode that when plugged in to the mapping that I stated, give me a set of data with my "desired mean" as the mean...

 

[JackBean]

Generating numbers randomly based on the normal distribution is a common question.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Random Number based on Normal Distribution
http://www.mrexcel.com/forum/showthread.php?t=465705
Also, this has info on:
Named Formulas for chart ranges
Conditional Formatting Formulas

This has also come up with the lognormal distribution.
Re: Lognormal Distribution being applied to IQR<o></o>
http://www.mrexcel.com/forum/showthread.php?t=473530<o></o>
<o></o>
I am not familiar with your method of making a skewed normal distribution by 'mapping' the numbers from the normal to the skewed normal.<o></o>
<o></o>
Here is how I would generate numbers randomly based on the skewed normal distribution.<o></o>
<o></o>
Use the Probability Distribution Function (PDF) for the skewed normal distribution, with the layout as shown below and in the above reference.<o></o>
<o></o>
=NORMDIST(D5,B$2,B$1,0)*NORMDIST(D5*skew,B$2,B$1,1)*2<o></o>
<o></o>
The skew can range from plus or minus infinity and if skew = 0 then it is the plain normal distribution, as shown below for STDin = 4 and AVGin = 0, and 'Select' 100 at 20 points, or bins.<o></o>
<o></o>
Input normal PDF<o></o>
skew = 0<o></o>


Now change skew from 0 to 3:

Excel Workbook
	A	B	C	D	E	F
1	STDin	4	Start	-5	STDout	2.3928564
2	AVGin	0	End	15	AVGout	3.0526316
3	Select	100	# Bins	20	NormSum	0.9499057
4	NormDist	Normalize	Cumulative	Bin	Count	Draw
5	8.075E-06	8.5E-06	0	-5	0	1.3157895
6	0.0001882	0.0001982	8.5E-06	-3.947368	0	5.5263158
7	0.0022972	0.0024183	0.0002067	-2.894737	0	2.3684211
8	0.0149891	0.0157796	0.002625	-1.842105	0	1.3157895
9	0.0541662	0.0570227	0.0184045	-0.789474	5	8.6842105
10	0.1150909	0.1211604	0.0754273	0.2631579	11	3.4210526
11	0.1583798	0.1667322	0.1965877	1.3157895	17	0.2631579
12	0.1610638	0.1695576	0.3633198	2.3684211	23	7.6315789
13	0.1376579	0.1449175	0.5328775	3.4210526	16	0.2631579
14	0.1066807	0.1123066	0.6777949	4.4736842	8	3.4210526
15	0.0768052	0.0808556	0.7901015	5.5263158	5	6.5789474
16	0.0515777	0.0542977	0.8709572	6.5789474	8	2.3684211
17	0.0323185	0.0340228	0.9252549	7.6315789	3	4.4736842
18	0.0188957	0.0198922	0.9592777	8.6842105	3	1.3157895
19	0.0103086	0.0108522	0.9791699	9.7368421	1	1.3157895
20	0.0052476	0.0055243	0.9900221	10.789474	0	4.4736842
21	0.0024926	0.002624	0.9955464	11.842105	0	1.3157895
22	0.0011047	0.001163	0.9981705	12.894737	0	1.3157895
23	0.0004569	0.000481	0.9993334	13.947368	0	2.3684211
24	0.0001763	0.0001856	0.9998144	15	0	4.4736842
25						0.2631579
26						2.3684211
27						1.3157895
28						4.4736842
29						2.3684211
30						-0.789474
31						4.4736842
32						2.3684211
33						2.3684211
34						1.3157895
35						3.4210526
36						2.3684211
37						3.4210526
38						0.2631579
39						2.3684211
40						2.3684211
41						3.4210526
42						2.3684211
43						5.5263158
44						4.4736842
...

The Cell Formula in Cell F5 is copied down through F104.

Cell Formulas
Range	Formula
A5	=NORMDIST(D5,B$2,B$1,0)*NORMDIST(D5*3,B$2,B$1,1)*2
B5	=A5/F$3
C5	=SUM(B4:B$5)*(ROW()>5)
D5	=D$1+(ROW()-5)*(D$2-D$1)/(D$3-1)
E5	=COUNTIF(F$5:INDEX(F:F,B$3+4),D5)
F5	=VLOOKUP(RAND(),C$5:INDEX(D:D,D$3+4),2)

Cell Formulas
Range	Formula
F1	=STDEVP(F5:INDEX(F:F,B3+4))
F2	=AVERAGE(F5:INDEX(F:F,B3+4))
F3	=SUM(A5:INDEX(A:A,D3+4))

Input skew normal PDF<o></o>
skew = 3


Here is a set of output numbers with 'Select' = 100


Here is a set of output numbers with 'Select' = 5000


Keep in mind that if you generate a set of numbers at random, the mean of the output will vary with each new set of numbers.
But with a large set of numbers the mean may not change much.

I found that I got strange results, like a cut-off skewed distribution, if AVGin<> 0.

It seems to me that you should be able to create a distribution that you want, and map it by shifting it horizontally to give the desired average.

References for skew normal distribution:
Wikipedia
And also:
http://azzalini.stat.unipd.it/SN/

 

[tusharm]

A standard normal distribution goes, *by definition,* from -infinity to +infinity. Multiplying by 0.13 changes the variability from 1 to 0.13 and adding 0.5 shifts the mean from 0 to 1 but you still get values between +/- infinity!

Using your mapping function, 1/2 of the times (when your normal random number < 0.5) your formula min+2*rand*(mean-min) will generate a *uniformly* distributed random number between min and 2*mean-min. Similarly, you will generate a different random number that too will be uniformly distributed when your first normal random variable is > 0.5

When you generate random numbers from a specified distribution, the distribution represents the population and the resulting numbers represent a sample. The sample statistics (like mean, variance, etc.) are *representative* of the population statistics and the two do not necessarily match. In fact, if they were to match, I would be suspicious of how the sample was created.

There are ways to generate a normally distributed sample with a specified min. and max. It's called a truncated normal. You can search google or bing for more on how to generate random variables from a truncated normal distribution.

Hi,

I need to randomly generate a set of 5000 numbers using just the following three inputs:
>Min
>Mean
>Max
They have to be normally distributed, but as the mean is never exactly half way between the min and max, the distribution will be skewed.

I am using =(NORMSINV(RAND())*0.13)+0.5 to give me 5000 random numbers that are normally distributed with a mean of 0.5 and (almost always) have a min of 0 and a max of 1.

I am then mapping these to the distribution that I require using
If random number < 0.5, f(x)=min+2*rand*(mean-min)
If random number = 0.5, f(x)=mean
If random number > 0.5, f(x)=mean+2*(rand-0.5)*(max-mean)

As expected this is giving me numbers within the predefined bounds, however the mean is way off what I was expecting.

For example, a min of 50,000, mean of 225,000 and a max of 1,000,000 give the following output:
>Min ~ 50,000-60,000 (this is close enough)
>Mean ~ 288,000-290,000 (this is no where near)
>Max ~ 950,000-1,000,000 (this is close enough)

I am not sure if there is a problem with my mapping formula, if it is to do with how I am generating the random numbers in the first place, or something else, but I am now stuck.

If anyone has any experience of this, or can offer any advice, then I would be most grateful!

Many thanks in advance,

Graham

 

[Mr. Tom]

I tried out the following, and the results are good. If you have larger numbers the mean is usually just a few percentage points off.

$B$1 = max value
$B$2 = min value
$B$3 = desired mean

=$B$2+($B$1-$B$2)*POWER(MIN(MAX(-3,STANDNORMINV(RAND())),3)/6+0.5,POWER(($B$1-B$3)/(B$3-$B$2),0.7))

How this works:
- In the middle is the Random Number from excel, evenly distributed
- From this I get the inverse of the standard normal distribution (result from minus to plus infinity)
- this I then reduce to an interval from -3 to +3 by using Excel MIN/MAX
- then I divide by six to get to an interval from -0.5 to +0.5
- (Result #1) then I add 0.5 to get to an interval from 0 to 1
- for the skew I use the second POWER term in where I divide the upper interval from my mean to my max by the lower interval from my min to my mean; the result is the relation between these two intervals
- I tried this out for the mean being somewhere between 25% and 75% of the interval between my max and my min
- (Result #2) the relation is then my base number; from trial-and-error I use 0.7 as the exponent; that brought me a small difference between the desired mean and the mean from the distribution
- (Result #3) finally I use Result #1 as base and Result #2 as exponent in another POWER statement
- this Result #3 I then use to calculate a part of the difference between my max and my min and add this part to my min

Here is how the distribution looks like:
https://docs.google.com/open?id=0Bwe0SO2ta65DOXlHMkFuakM5Rjg

And here are the results if I draw 10000 random numbers.
https://docs.google.com/open?id=0Bwe0SO2ta65DTE5rdlhIZ0R4MWc

Hi,

I need to randomly generate a set of 5000 numbers using just the following three inputs:
>Min
>Mean
>Max
They have to be normally distributed, but as the mean is never exactly half way between the min and max, the distribution will be skewed.

I am using =(NORMSINV(RAND())*0.13)+0.5 to give me 5000 random numbers that are normally distributed with a mean of 0.5 and (almost always) have a min of 0 and a max of 1.

I am then mapping these to the distribution that I require using
If random number < 0.5, f(x)=min+2*rand*(mean-min)
If random number = 0.5, f(x)=mean
If random number > 0.5, f(x)=mean+2*(rand-0.5)*(max-mean)

As expected this is giving me numbers within the predefined bounds, however the mean is way off what I was expecting.

For example, a min of 50,000, mean of 225,000 and a max of 1,000,000 give the following output:
>Min ~ 50,000-60,000 (this is close enough)
>Mean ~ 288,000-290,000 (this is no where near)
>Max ~ 950,000-1,000,000 (this is close enough)

I am not sure if there is a problem with my mapping formula, if it is to do with how I am generating the random numbers in the first place, or something else, but I am now stuck.

If anyone has any experience of this, or can offer any advice, then I would be most grateful!

Many thanks in advance,

Graham