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.

22 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

  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

  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

  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 *