# Calculating confidence interval in Excel

June 09, 2019

This post describes how to calculate a confidence interval on a sample set of more than 30 observations. For this example let’s start out with some data of number of e-commerce orders by country (**Germany** in this case).

In this example we’re going to use a 95% confidence interval (CI) that allows us to say something along the lines of **“with 95% confidence we can predict that we will see between x and y number of orders coming in from Germany tomorrow”**. To start out i’ve organized the values with two columns (**date** and **country**).

To start calculating the confidence interval, I’m gonna start filling out a bunch of values in some column to the left of the table, E or F for example. Let’s say my table is named **Orders** and the column containing order quantity for Germany is called **GERMANY**, then I will get down the following (using column F):

- Number of observations (
**=COUNT(Orders[GERMANY])**) - Average (
**=AVERAGE(Orders[GERMANY])**) - Standard Deviation (
**=STDEV.S(Orders[GERMANY]**) - Confidence Level - for a 95% confidence level this means 0.95
- Significance: (1 - Confidence level), ie. 0.05 (
**=1-F6**) - Z-Score: The number of standard deviations from the average where a value is located. In this case we’re looking for the z-score within which 95% of the values lie, +/- the number of standard deviations from the mean (
**=ABS(NORM.S.INV(F7/2))**). Absolute values are easier to deal with which is why the ABS function is used. - Standard error - calculated by dividing the standard deviation by the square root of the number of measurements (
**=F4/(SQRT(F2))**). Ie. the expected error expected to occur per standard deviation. - Margin of error - the z-score multiplied by the standard error
- Lower Limit - Now we can solve for the lower limit which is the same as the sample mean - the margin of error
- Upper Limit - the sample mean - the margin of error

In other the words the 95% confidence interval lies in the range of 6.9 - 8, which is to say we can, with 95% confidence expect 6.9 - 8 orders from Germany any given day.

Written by **Johan Osterberg** who lives and works in Gothenburg, Sweden as a developer specialized in e-commerce. Connect with me on Linkedin