Uniform Distribution

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)

Uniform distribution pdf

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

Observation: The corresponding cumulative distribution function is

Uniform distribution function

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.

26 Responses to Uniform Distribution

  1. Maruim says:

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

  2. Stefan says:

    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)

    • Charles says:

      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

    • r says:

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

  3. Ben says:

    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?

  4. Artem says:

    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)

    • Charles says:

      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

    • r says:

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

  5. Gopal Shah says:

    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.

    • Charles says:

      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

  6. Gopal Shah says:

    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

    • Charles says:

      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

      • Gopal Shah says:

        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

        • Gopal Shah says:

          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

  7. revathi says:

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

  8. Avi says:

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

    • Charles says:

      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

  9. Raquel says:

    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

    • Charles says:

      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

    • r says:

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

  10. Antoine says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *