Dec 15

Modeling a sample from a normal distribution in Excel - Exercise 2.4

Modeling a sample from a normal distribution.

Read Exercise 2.4 in the book and Unit 6.6 for the theory. In this video, along with the sample from a normal distribution and a histogram, we construct the density and cumulative distribution function with the same mean and standard deviation.

Simulation steps

  1. μ and σ are chosen by the user. I select them so as to model temperature distribution in Almaty.
  2. temp (temperature) are just integer number from 14 to 38
  3. Use the Excel command norm.dist with required arguments. The last argument should be "false" to produce a density
  4. The last argument should be "true" to give a cumulative distribution
  5. A combination of the commands norm.dist and rand gives simulated temperature values
  6. Next we define the bins required by the Excel function histogram, which is a part of the Data analysis toolpack (needs to be installed and activated)

The fact that Excel maintains links between data and results is very handy. That is, each time the data is renewed, the histogram will change. Pressing F9 (recalculate) you can see the histogram changing together with the sample.

Another interesting fact is that sometimes randomly generated numbers are not realistic. If observed in practice, they could be called outliers. But here they are due to the imperfect nature of the normal distribution, which can take very large (negative or positive) values with a positive probability.


Modeling a sample from a normal distribution - click to view video

Leave a Reply

You must be logged in to post a comment.