Confidence Levels / Standard Deviation / Bell Curves & Excel 2007

Posted on 2009-04-16
Last Modified: 2012-06-27
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
Question by:mogsey
    LVL 1

    Accepted 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

    Author Closing Comment

    This is an exceptional answer and deserves more than what is on offer, I dont speak portugues but thankyou in your native tounge
    LVL 1

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
    Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
    The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now