Standard deviations and confidence intervals in spreadsheets

In connection with average and median values, you also have an interest in knowing the level of uncertainty for your calculations. For this you use standard deviations, which is an evaluation of the variations in the measurements, and the confidence interval, which is an evaluation of the uncertainty of the calculated average/median value.

Standard deviations is a measurement of the natural variation in a set of observations. Here you should be aware, that there is several ways of calculating standard deviations, depending on a number of conditions, and you should be aware, that not all methods are available for all file formats. Also you should be aware, that the same functions may have different names, depending on the file formats.

For Microsoft Excel you have the following functions:

• STDEV.P() if the observation set is the ENTIRE population. Illegal values, e.g. text, will be ignored.
• STDEV.S() if the observation set is A PART OF THE ENTIRE population. Illegal values, e.g. text, will be ignored.
• STDEVA() if the observation set is A PART OF THE ENTIRE population. Illegal values, e.g. text, are set to 0 and included in the calculation.
• STDEVPA() if the observation set is the ENTIRE population. Illegal values, e.g. text, are set to 0 and included in the calculation.

In the earlier versions of Excel, when using the file format .xls, you only had the function STDEV(), which is equivalent to STDEV.P().

To illustrate the difference between the four types of standard deviation, the image below shows an observation set consisting of 5 legal values + 1 illegal value (text):




For OpenOffice Calc you have the following functions:

• STDEVP() if the observation set is the ENTIRE population. Illegal values, e.g. text, will be ignored.
• STDEV() if the observation set is A PART OF THE ENTIRE population. Illegal values, e.g. text, will be ignored.
• STDEVA() used if the observation set is A PART OF THE ENTIRE population. Illegal values, e.g. text, are set to 0 and included in the calculation.
• STDEVPA() if the observation set is the ENTIRE population. Illegal values, e.g. text, are set to 0 and included in the calculation.

To illustrate the difference between the four types of standard deviation, the image below shows an observation set consisting of 5 legal values + 1 illegal value (text):

In the same way as you want to know the natural variation in your observations, you may also want some measurement on the expected accuracy of your average/median value. This is called a confidence interval. An interval from the average/median value, where you can find a specified percentage of your observations. In reality, it is a qualification of your standard deviation, where you look at the size of the standard deviation in relation to the number of observations on which it is calculated.

Like the standard deviations, there is some differences in the available functions, depending on the file formats.

For Microsoft Excel you have the following functions:

• CONFIDENCE.NORM() if you have a normal distribution of the numbers in the observation set (Z-intervals).
• CONFIDENCE.T() if you DON'T have a normal distribution of the numbers in the observation set (T-intervals).

The functions uses three variables:

1. Alpha (also written α): The significance level. The confidence level is equal to 100*(1-α), so a 95% confidence interval is equal to α=0,05 and a 99% confidence interval is equal to α=0,01 etc.
2. Standard deviation: Usually you write the name of the cell, where this is calculated, but writing a fixed value is perfectly fine.
3. Size: The size of the observation set. Here you have to be very aware whether you have illegal values in the observation set, and if you do, are you using a standard deviation that includes illegal values or not!

In the earlier versions of Excel, when using the file format .xls, you only had the function CONFIDENCE(), which is equivalent to CONFIDENCE.NORM().

For at illustrere forskellen på de to typer konfidensinterval, ses her to beregninger af 95% konfidensintervaller, baseret på en STDEV.P()-værdi:




For OpenOffice Calc you only have the function CONFIDENCE(), which is the confidence interval, under the assumption that you have a normal distribution of the observations.
The function uses three variables:

1. Alpha (also written α): The significance level. The confidence level is equal to 100*(1-α), so a 95% confidence interval is equal to α=0,05 and a 99% confidence interval is equal to α=0,01 etc.
2. Standard deviation: Usually you write the name of the cell, where this is calculated, but writing a fixed value is perfectly fine.
3. Size: The size of the observation set. Here you have to be very aware whether you have illegal values in the observation set, and if you do, are you using a standard deviation that includes illegal values or not!

To illustrate the calculation of a confidence interval, here we see the calculation of a 95% confidence interval, based on a STDEVP() value:

For some reason Microsoft has decided to have localized function names, so for non-English versions of Excel, you need to use the translated function names. This is as far as I have gotten, finding the localized function names for this chapter:

Catalan	DESVEST()
Czech	SMODCH.VÝBĚR()
Danish	STDAFV()
Dutch	STDEV()
English	STDEV()
Finnish	KESKIHAJONTA()
French	ECARTYPE()
Galician	DESVEST()
German	STABW()
Hungarian	SZÓRÁS()
Italian	DEV.ST()
Norwegian	STDAV()
Polish	ODCH.STANDARDOWE()
Portuguese (BR)	DESVPAD()
Portuguese (PT)	DESVPAD()
Russian	СТАНДОТКЛОН()
Spanish	DESVEST()
Swedish	STDAV()
Turkish	STDSAPMA()

Catalan	DESVEST.P()
Czech	SMODCH.P()
Danish	STDAFV.P()
Dutch	STDEV.P()
English	STDEV.P()
Finnish	KESKIHAJONTA.P()
French	ECARTYPE.PEARSON()
Galician	DESVEST.P()
German	STABW.N()
Hungarian	SZÓR.S()
Italian	DEV.ST.P()
Norwegian	STDAV.P()
Polish	ODCH.STAND.POPUL()
Portuguese (BR)	DESVPAD.P()
Portuguese (PT)	DESVPAD.P()
Russian	СТАНДОТКЛОН.Г()
Spanish	DESVEST.P()
Swedish	STDAV.P()
Turkish	STDSAPMA.P()

Catalan	DESVEST.M()
Czech	SMODCH.VÝBĚR.S()
Danish	STDAFV.S()
Dutch	STDEV.S()
English	STDEV.S()
Finnish	KESKIHAJONTA.S()
French	ECARTYPE.STANDARD()
Galician	DESVEST.M()
German	STABW.S()
Hungarian	SZÓR.M()
Italian	DEV.ST.C()
Norwegian	STDAV.S()
Polish	OODCH.STANDARD.PRÓBKI()
Portuguese (BR)	DESVPAD.A()
Portuguese (PT)	DESVPAD.S()
Russian	СТАНДОТКЛОН.В()
Spanish	DESVEST.M()
Swedish	STDAV.S()
Turkish	STDSAPMA.S()