• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3086
  • Last Modified:

Confidence Levels / Standard Deviation / Bell Curves & Excel 2007

Evening or Morning All,

Before I start I have spent days on this so any help is appreciated!

I have been asked to calculate a 95% confidence of delivering an order in time, I would like to be able to put this into graphical form (Bell Diagram Possibly) and be able to put describe it also.

My test data is;

Run No.      Duration (minutes)      Production
1      90      580
2      40      380
3      60      455
4      85      440
5      115      700
6      65      450
7      30      90
8      60      450
9      85      550
10      120      740
11      110      680
12      90      500
13      55      450
14      85      450
15      25      80
16      70      455
17      75      400
18      80      530
19      115      710
20      85      450

Using stdev I have calculated the standard deviance to be 172.04 but I dont understand how to caluclate the 95% Confidence.

Cheers in advance Mogsey
  • 2
1 Solution

If I understood you´d like to print a normal curve based on the sample of the durations.
If so you can do the following:
I am considering that duration data are in range C2:C21

1. Type Average in cell A23
2. In cell B23, type the formula =Average(C2:C21)
3. Type StdDev in cell A24
4. In cell B24, type the formula =StDev(C2:C21)
5. Type Confidence in cell A25
6. In cell B25, type the value 95%
7. Type No. items cell A26
8. In cell B26, type the formula =Count(C2:C21)
9. Type Confidence in cell A27
10. In cell B27, type the formula  =Confidence(1-B25, B24, B26)

Now you will calculate the Normal distribution. I suggest 200 points.
Type number 1 in cell A30
Select A30; In the Tab Start, choose Fill Series Command (on the right side, just below AutoSum) e fill  Columns, Starting in 1, Increment 5, Limit 1000. So you will have 1, 6, 11, 16 etc. Why that? The durations you have are between the interval of 1 to 1000.

Select B30 e type the formula =NormDist(A30, $B$23; $B$24; False)
Copy this formula until the last value in the A column (A229).

Now you will calculate de points (x, y) to plot the confidence interval (two vertical lines)
- Select the cell D23 and type Inf. Bound
- Select the cell E23 and type the formula =B23-B27
- Select the cell E24 and type the formula =E23
- Select the cell F23 and type the formula =0
- Select the cell F24 and type the formula =Max(A30:A229)
- Select the cell H23 and type Inf. Bound
- Select the cell I23 and type the formula =B23+B27
- Select the cell I24 and type the formula =I23
- Select the cell J23 and type the formula =0
- Select the cell J24 and type the formula =Max(A30:A229)

Now the chart. Select the interval A30:B229. Insert a Scattered Chart (only with lines, without the points) And you will have the normal distribution.
Now the confidence interval, select the chart, tab Design (in the ribbon) and choose Select data.
- Click em Add (Serie)
- For the name select D23
- For X values select: E23:E24
- For Y values select: F23:F24
- Click OK

The last, the superior bound:
- Click em Add (Serie)
- For the name select H23
- For X values select: I23:I24
- For Y values select: J23:J24
- Click OK

Good Luck.
Carlos César Tanaka
Curso de Excel Avançado
mogseyAuthor Commented:
This is an exceptional answer and deserves more than what is on offer, I dont speak portugues but thankyou in your native tounge
I am satisfied you have got you want. Sorry if I have written something wrong; yes I really don´t  speak English.
Carlos César Tanaka

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now