When you ask for a random set of say 100 numbers between 1 and 10, you are looking for a sample from a uniform distribution, where *α* = 1 and *β* = 10 according to the following definition.

**Definition 1**: The **uniform distribution** has probability density function (pdf)

where *α* and *β* are any parameters with *α* < *β*.

**Observation**: The corresponding cumulative distribution function is

The inverse cumulative distribution function is

*I*(*p*) = *α* + *p*(*β* − *α*)

Other key statistical properties are:

- Mean = (
*α + β*) / 2 - Median = (
*α + β*) / 2 - Mode = any
*x*,*α ≤ x ≤ β* - Range = (-∞, ∞)
- Variance = (
*β – α*)^{2}/ 12 - Skewness = 0
- Kurtosis = -1.2

**Real Statistics Functions**: Excel doesn’t provide any functions for the uniform distribution. Instead you can use the following functions provided by the Real Statistics Resource Pack.

**UNIFORM_DIST**(*x, α, β, cum*) = the pdf of the uniform distribution *f*(*x*) at *x* when *cum* = FALSE and the corresponding cumulative distribution function *F*(*x*) when *cum* = TRUE.

**UNIFORM_INV**(*p, α, β*) = *x* such that UNIFORM_DIST(*x, α, β*, TRUE) = *p*. Thus UNIFORM_INV is the inverse of the cumulative distribution version of UNIFORM_DIST.

Dear Charles,

I’ve been browsing your site in search for a solution to my question. But I am unsure what method to employ.

I am working in Excell. My sample consists of 66 columns and 250 rows. Of the 1650o datapoints, 290 display a one; the rest are zero.The distribution of 1’s over the 66 columns is relevant to me and I wish to determine whether this distribution is statistically different from a random distribution.

Could you please give me some pointers (binom/poisson/normal dist?; How can I efficiently perform simulations?)

Kind regards,

Antoine

Antoine,

See the webpage http://www.real-statistics.com/sampling-distributions/simulation/ for information about simulations

See the webpage http://www.real-statistics.com/non-parametric-tests/one-sample-runs-test/ for information about testing random runs of 0’s and 1’s

Charles

Dear Charles,

I am trying to generate a CDF with a uniform distribution between -55 and -45 with 1000 samples. I can’t seem to get the function to work. I typed in uniform_dist(1000,-55,-45,true), and all I seem to be getting is 1’s. Am I doing something wrong?

Regards,

Raquel

Raquel,

Since the first argument (1000) is larger than the third argument(-45), the cdf is always 1. Generally you should choose a value for the first argument that is between the second and third arguments. E.g. uniform_dist(-51,-55,-45,true) has the value .4

Charles

Just type RAND()*(b-a) + a. Don’t need to download any package.

I have generated the UNIFORM_DIST value. Now how do I get the graph in excel for presentation.

Avi,

The following approach can be used to generate a graph of any distribution with probability density function f(x) in Excel in say the range a to b. For simplicity I will assume that a = 2 and b = 5. I will also assume that you want the granularity of the graph to be in units of size .1 (you can choose whatever granularity you like).

Place the value of 2 (i.e. a) in cell A1. Next place the formula =A1+.1 in cell A2 (here .1 is the granularity). Next highlight the range A2:30 and press Ctrl-D. This will place the numbers 2, 2.1, 2.2, …, 4.9, 5.0 in the range A1:A31 (here 5 is b). Next, place the formula for f(x) in cell B1 where x is replaced by the cell reference A1 — for your problem you use UNIFORM_DIST(A1,2,5,FALSE). Now highlight the range B1:B31 and press Ctrl-D. Finally, highlight the range A1:B31 and create a scatter plot by choosing Insert > Charts|Scatter.

The resulting graph will be the horizontal line y = 1/3 between 2 and 5. In general the graph will be the horizontal line y = 1/(b-a) between x = a and x = b.

Charles

how to do monte carlo simulation in excel 2007 and how to calculate prediction using uniform distribution

Revathi,

See the following webpage: Simulation.

Charles

Sir,I want to generate U(0,1),for sample size 50 with between 0.72 to 0.92 where p2=p3,p1=2*p2 and p4=1-(p1+p2+p3).Optimize sample size with n1,n2,n3 and n4.Is it possible in Excel 2007.Give me help

You can certainly generate a sample of size 50 that follows a uniform distribution U(0,1), namely by using the RAND() formula, but I don’t understand the other constraints that you have listed.

Charles

Resp.Sir,

I have applied Multinomial Distribution.I have to generate the probabilities p1+p2+p3<=0.8 and p1=2p2=2p3.Generate 50 random nos using U(0,1).Obtain p1,p2,p3.Then using Monte-Carlo Method and estimate n1,n2,n3.Is it possible in Excel 2007

Resp.Sir,

I have applied Multinomial Distribution.I have to generate the probabilities p1+p2+p3<=0.8 and p1=2p2=2p3.Generate 50 random nos using U(0,1).Obtain p1,p2,p3.Then using Monte-Carlo Method and estimate n1,n2,n3.Is it possible in Excel 2007

Reply

Resp. Sir

I want to generate U(0,1),with 50 size.where p1=2*p2,p2=p3 and p4=1-(p1+p2+p3).

Use the monte carlo method to optimize n1,n2,n3 and n4.Give early as possible.

Gopal,

You have repeated this problem to me several times now, but I am sorry to say that I still don’t understand the question well enough to give you an answer.

Charles

Charles,

First of all thank you for porting your add-in to excel 2016. Now I have opportunity to use it. I’ve recently installed it and currently trying to learn it’s functionality . I’ve found problem with UNIFORM_DIST function.

I make a table with two columns. First column is x values. Second column: UNIFORM_DIST(first_column_value_of_same_row,1,3,0). Third row: UNIFORM_DIST(first_column_value_of_same_row,1,3,1).

0 0 0

1 0.5 0

2 0.5 0.5

3 0.5 1

4 0 1

Problem: probabilities in second column don’t sum up to 1. Also the third row is made considering (α, β] interval (so α is not included – I’m not sure if that’s intentional)

Artem,

The correct form of the formula is UNIFORM_DIST(x,0,3,cum). Thus the table should look like

x pdf cdf

0 0.333333333 0

1 0.333333333 0.333333333

2 0.333333333 0.666666667

3 0.333333333 1

Charles

Just type RAND()*(b-a) + a. Don’t need to download any package.

I’ve downloaded the Resource Pack and ticked the Realstats add-in but excel isn’t recognising UNIFORM_DIST as a formula. Any ideas/tips on what I may be doing wrong?

Ben,

What do you see when you enter the formula =VER()

Charles

Just type RAND()*(b-a) + a. Don’t need to download any package.

Hello,

My Excel doesn’t recognize the UNIFORM_INV after installing and checking it on in Excel 2016.. What can I do? (VER() doesn’t work as well)

This probably means that you haven’t installed the Real Statistics addin. These are not standard Excel capabilities. You need to download the Real Statistics addin. It is free.

Charles

Just type RAND()*(b-a) + a. Don’t need to download any package.

My question is ( does rectangular distribution haveno mode or any values between alpha and beta.) ???

Any value between alpha and beta.

Charles