Data Analysis, Error and Uncertainty – data analysis using Excel
Produced by Graham Currell, University of the West of England, Bristol and David Read, University of Southampton, in association with:
● Royal Society of Chemistry, ‘Discover Maths for Chemists’ website, and
● Essential Mathematics and Statistics for Science, 2nd Edition
Graham Currell and Antony Dowman, WileyBlackwell, 2009
This study unit uses video to demonstrate the use of Excel in the analysis of experimental data and its uncertainty. The Excel files used in the data analysis examples and videos can be downloaded here:
ExcelDataUncert01.xlsx for analyses 1 and 2, and
The study unit is divided into four main sections:
Introduction – provides an overview of the important methods of data analysis using Excel, together with links to video tutorials on basic skills and selfassessment study guide/tutorials on linear regression.
1. Analysis of replicate data – demonstrates the use of equations, functions and data analysis tools, to interpret the results of repeated measurements of a single experimental value. The data represents replicate measures of the pressure, p, of a gas.
2. Analysis of linear data – demonstrates the use of regression analysis and graphical presentation to interpret the experimental results for a linear relationship between two variables. The data uses the variation of pressure, p, against temperature, T, of an ideal gas.
3. Analysis of linear calibration data – demonstrates the analysis of spectrophotometric data, using correlation coefficients, data residuals, and a calculation of the 95% confidence interval of the measurement of concentration using the calibration line of bestfit.
Introduction
It is possible to:
· Use Excel functions to perform specific calculations
e.g. =SQRT(B4) will calculate the square root of the value in cell B4.
· Write equations directly into Excel cells,
e.g. =B5*B6/SQRT(B4) will multiply the contents of B5 and B6 and divide by the square root of B4.
· Use Data Analysis tools. These are not normally loaded when Excel is first installed, but can be added later (see video) using AddIns.
Additional video help on the use of Excel 2007 is available:
Important notes on calculations in Excel:
· Equations and functions are dynamic. Their results change if the source data is changed, e.g. the value of SQRT(B4) will change if the value in B4 is changed.
· The calculations in the Data Analysis tools are static. They are a singleshot calculation, whose results do NOT change if the source data changes.
Self Assessment Study Guide:
Analysis 1: Experimental uncertainty (error) in replicate measurements
It is common (and good) practice to obtain repeated (replicate) measurements of the same experimental value, e.g. measurements of gas pressure (in pascals):
This data is analysed using:
Equations and functions to calculate – see video Replicate Measurements A
· Mean value, x, (p171) using function AVERAGE(data)
· Sample standard deviation, s, (p173) using function STDEV(data)
· Sample size, n, using function COUNT(data)
· Standard uncertainty, u, (p221) using the equation, u = s/√n
· Degrees of freedom, df, (p222) using the equation, df = n – 1
· tvalue (p222) for 95% confidence using the function = TINV(0.05,df)
· 95% Confidence deviation, Cd, (p221) using the equation, Cd = t*s/√n
· 95% Confidence interval (p221), minimum and maximum values, using CI = x ± Cd
(NB Do NOT use the function CONFIDENCE for experimental sample data)
(The page numbers given in brackets above refer to the text: Essential Mathematics and Statistics for Science, 2nd Ed, by G Currell and A A Dowman)
The Descriptive Statistics option in Data Analysis tools to – see video Replicate Measurements B
· Calculate the same values as obtained by the equations and functions above
Excel demonstration of the effect of random experimental variations – see video Replicate Measurements C
Analysis 2: Experimental uncertainty (error) in simple linear data plot
A typical set of linear data can be described by the change of the pressure, p, (in pascals) of an ideal gas as a function of the temperature, T, in degrees kelvin.
There is doubt surrounding the accuracy of most statistical data—even when following procedures and using efficient equipment to test. Excel lets you calculate uncertainty based on your sample’s standard deviation.
There are statistical formulas in Excel we can use to calculate uncertainty. And in this article, we will calculate the arithmetic mean, standard deviation and the standard error. We will also look at how we can plot this uncertainty on a chart in Excel.
We will use the following sample data with these formulas.
This data shows five people that have taken a measurement or reading of some kind. With five different readings, we have uncertainty over what the real value is.
Arithmetic Mean of Values
When you have uncertainty over a range of different values, taking the average (arithmetic mean) can serve as a reasonable estimate.
This is easy to do in Excel with the AVERAGE function.
We can use the following formula on the sample data above.
Standard Deviation of the Values
The standard deviation functions show how widely spread your data is from a central point (the mean average value we calculated in the last section).
Excel has a few different standard deviation functions for various purposes. The two main ones are STDEV.P and STDEV.S.
Each of these will calculate the standard deviation. The difference between the two is that STDEV.P is based on you supplying it with the entire population of values. STDEV.S works on a smaller sample of that population of data.
In this example, we’re using all five of our values in the data set, so we will work with STDEV.P.
This function works in the same way as AVERAGE. You can use the formula below on this sample of data.
The result of these five different values is 0.16. This number tells us how different each measurement typically is from the average value.
Calculate the Standard Error
With the standard deviation calculated, we can now find the standard error.
The standard error is the standard deviation divided by the square root of the number of measurements.
The formula below will calculate the standard error on our sample data.
Using Error Bars to Present Uncertainty in Charts
Excel makes it wonderfully simple to plot the standard deviations or margins of uncertainty on charts. We can do this by adding error bars.
Below we have a column chart from a sample data set showing a population measured over five years.
With the chart selected, click Design > Add Chart Element.
Then choose from the different error types available.
You can show a standard error or standard deviation amount for all values as we calculated earlier in this article. You can also display a percentage error change. The default is 5%.
For this example, we chose to show the percentage.
There are some further options to explore to customize your error bars.
Doubleclick an error bar in the chart to open the Format Error Bars pane. Select the “Error Bars Options” category if it is not already selected.
You can then adjust the percentage, standard deviation value, or even select a custom value from a cell that may have been produced by a statistical formula.
Excel is an ideal tool for statistical analysis and reporting. It provides many ways to calculate uncertainty so that you get what you need.
The other day I read an article titled “Five easy steps for adding measurement uncertainties to your calibration data.” It described a very easy process of copying cells from one Microsoft Excel® file to another, then editing the data in the cells. As I continued to read, the article concluded with how this copy and paste operation took a fraction of the time when compared to other software tools built for metrology.
As a software engineer this was counterintuitive to everything I was ever taught as a programmer. “Copy & Paste” was bad mojo, very bad, extremely bad! Don’t copy & paste I was always told, it just creates more problems than it’s worth. Reading this article caught me by surprise; how could it be such a time saver when compared to tools built for metrology? If copy and paste is such a bad idea for programmers, why didn’t the same rule not apply to metrology and uncertainties?
So first some background: As a junior programmer, copy & paste was considered bad programming for two major reasons. First, copying code created multiple copies of the same code in memory. This was highly inefficient when computers have limited memory. So as it was explained to me, create a function and call it instead of duplicating code. Anytime you think you need to copy something, first think of how you can make it a reusable function.
The second and most important problem with copy and pasting code was the potential of duplicating errors. If there is an error in the code that was copied, that error would be duplicated. If there is an error, now it exists in multiple places, and you have no idea where or how many times the error was duplicated. If the error existed in a single function then the error and its effects on the rest of the solution are easier to support.
So it is only reasonable to assume the same problem applies to metrology and uncertainty calculations! If there is an error or problem in the cells of the first spread sheet, they will have been propagated to an unknown number of other Excel files. Just like in software, errors that are found later present a huge cost in fixing them when compared to the initial cost of development. That is why copy and paste is frowned upon in software development.
But for metrology the problem is bigger! We know different requirements require different uncertainty calculations. And there are some major changes on the horizon with 17025. Updating a single function or a set of functions will prove to be much easier than updating all those Excel files.
Now understand, I use the hell out of Excel. I would rather open a new spreadsheet to make a quick calculation than use the calculator application. I have tons and tons of Excel files. And yes, I have used it to calculate uncertainties.
What I don’t like about Excel, when it comes to using it as a metrology based uncertainty calculator or for calibration data collection, is that it mixes data and function in a single file. At first it seems like a good idea, but it is not. Excel is a BandAid® that puts both data and formulas in an unstructured file. If the whole goal is to create a file that you will never use again, then Excel is a good fit. But if you want to have data that you can use in the future, Excel—when it comes to metrology—is more problem than solution because it is unstructured.
If your company is serious about metrology and has a focus on using the data that is collected during a calibration, then I suggest you invest in a database. Putting your calibration results in a data table will allow you to recall that data and use it for other things like interval and reliability analysis. Placing your uncertainty calculations inside of your data collection tools is also very bad practice. There is a reason we call them Estimated Measurement Uncertainties; they are estimates and those estimates change. And you never have to rerun a calibration because your estimated uncertainties are part of the data collection process. Data collecting is data collection and uncertainty calculations are something that should be done post process, but that is a topic for another day.
Related Articles
 How to Normalize in Excel
 How to Get a Facebook Share Link on WordPress
 How to Find the Tangent on a Graph in Excel
 How to Make a Graph on Excel With a Cumulative Average
 How to Convert a Spreadsheet Into a CommaDelimited String
 How to Transition for Starting a New Paragraph in Excel
Calculating the uncertainty of a statistical value is helpful in a range of business applications such as evaluating customer feedback, testing the quality of assembly line products and analyzing historical returns on a stock. Given a sample size of data, the standard deviation will tell you how much variation within the sample there is from the average. Uncertainty arises from the size of the sample size and from which members of the population you happen to be including. With Microsoft Excel, you can measure the uncertainty of the sample’s standard deviation by calculating the standard error of the mean.
Step 1
Open your spreadsheet in Excel.
Step 2
Enter the values for your sample size, one per cell, in an empty column. For example, enter the values “2,” “4,” “6,” “8” and “10” (omit the quotation marks here and throughout) in the cells A1, A2, A3, A4 and A5.
Step 3
Calculate the sample standard deviation by clicking an empty cell, such as B1, and typing “=STDEV.S(A1:A5).” Replace “A1:A5” with the range of cells containing the values for your sample. Using the previous example, the standard deviation of the sample size is 3.16.
Step 4
Calculate the standard error of the mean by clicking an empty cell and typing “=B1/SQRT(COUNT(A1:A5)).” Replace “A1:A5” with the range of cells containing the values for your sample. Replace “B1” with the cell containing the standard deviation calculation from the previous step. Using the previous example, the standard error of the sample is 1.41.
 Microsoft: STDEV.S Function
 Microsoft: SQRT
 Mathworld: Standard Error
 Mathworld: Standard Deviation
Sean Mann has been a freelance writer since 2010. With thorough knowledge and experience in technological fields such as computer software, hardware, the internet and programming, he creates online content for various websites. Mann has a Bachelor of Science in computer science from Ohio State University.
Data Analysis, Error and Uncertainty – data analysis using Excel
Produced by Graham Currell, University of the West of England, Bristol and David Read, University of Southampton, in association with:
● Royal Society of Chemistry, ‘Discover Maths for Chemists’ website, and
● Essential Mathematics and Statistics for Science, 2nd Edition
Graham Currell and Antony Dowman, WileyBlackwell, 2009
This study unit uses video to demonstrate the use of Excel in the analysis of experimental data and its uncertainty. The Excel files used in the data analysis examples and videos can be downloaded here:
ExcelDataUncert01.xlsx for analyses 1 and 2, and
The study unit is divided into four main sections:
Introduction – provides an overview of the important methods of data analysis using Excel, together with links to video tutorials on basic skills and selfassessment study guide/tutorials on linear regression.
1. Analysis of replicate data – demonstrates the use of equations, functions and data analysis tools, to interpret the results of repeated measurements of a single experimental value. The data represents replicate measures of the pressure, p, of a gas.
2. Analysis of linear data – demonstrates the use of regression analysis and graphical presentation to interpret the experimental results for a linear relationship between two variables. The data uses the variation of pressure, p, against temperature, T, of an ideal gas.
3. Analysis of linear calibration data – demonstrates the analysis of spectrophotometric data, using correlation coefficients, data residuals, and a calculation of the 95% confidence interval of the measurement of concentration using the calibration line of bestfit.
Introduction
It is possible to:
· Use Excel functions to perform specific calculations
e.g. =SQRT(B4) will calculate the square root of the value in cell B4.
· Write equations directly into Excel cells,
e.g. =B5*B6/SQRT(B4) will multiply the contents of B5 and B6 and divide by the square root of B4.
· Use Data Analysis tools. These are not normally loaded when Excel is first installed, but can be added later (see video) using AddIns.
Additional video help on the use of Excel 2007 is available:
Important notes on calculations in Excel:
· Equations and functions are dynamic. Their results change if the source data is changed, e.g. the value of SQRT(B4) will change if the value in B4 is changed.
· The calculations in the Data Analysis tools are static. They are a singleshot calculation, whose results do NOT change if the source data changes.
Self Assessment Study Guide:
Analysis 1: Experimental uncertainty (error) in replicate measurements
It is common (and good) practice to obtain repeated (replicate) measurements of the same experimental value, e.g. measurements of gas pressure (in pascals):
This data is analysed using:
Equations and functions to calculate – see video Replicate Measurements A
· Mean value, x, (p171) using function AVERAGE(data)
· Sample standard deviation, s, (p173) using function STDEV(data)
· Sample size, n, using function COUNT(data)
· Standard uncertainty, u, (p221) using the equation, u = s/√n
· Degrees of freedom, df, (p222) using the equation, df = n – 1
· tvalue (p222) for 95% confidence using the function = TINV(0.05,df)
· 95% Confidence deviation, Cd, (p221) using the equation, Cd = t*s/√n
· 95% Confidence interval (p221), minimum and maximum values, using CI = x ± Cd
(NB Do NOT use the function CONFIDENCE for experimental sample data)
(The page numbers given in brackets above refer to the text: Essential Mathematics and Statistics for Science, 2nd Ed, by G Currell and A A Dowman)
The Descriptive Statistics option in Data Analysis tools to – see video Replicate Measurements B
· Calculate the same values as obtained by the equations and functions above
Excel demonstration of the effect of random experimental variations – see video Replicate Measurements C
Analysis 2: Experimental uncertainty (error) in simple linear data plot
A typical set of linear data can be described by the change of the pressure, p, (in pascals) of an ideal gas as a function of the temperature, T, in degrees kelvin.
Written by cofounder Kasper Langmann, Microsoft Office Specialist.
The ‘CONFIDENCE’ function is one of Excel’s oldest statistical functions .
The ‘CONFIDENCE’ function calculates the confidence value for the confidence interval of a data set . A confidence interval is a defined range of values that might contain the true mean of a data set .
In this tutorial, you’ll get to know more about the ‘CONFIDENCE’ function, look under its hood, and figure out how to make it work.
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.
Table of Content
Get your FREE exercise file
Before you start:
Throughout this guide, you need a data set to practice.
I’ve included one for you (for free).
Download it right below!
Download the FREE Exercise File
What is the ‘CONFIDENCE’ function?
The ‘CONFIDENCE’ function is an Excel statistical function that returns the confidence value using the normal distribution .
In turn, the confidence value is used to calculate the confidence interval (or CI) of the true mean (or average) of a population.
It’s a way to represent the uncertainty of your data in a scientific way . It gives the reader or user a socalled ‘margin of error’.
Most of the time, people use 95% as the confidence level. Meaning, out of 100 repeated experiments, the true mean is found in 95 of them.
However, a 95% confidence level is not a standard. You can choose your own confidence level, although, people commonly use 90% – 99% to well… instill confidence. 😊
After you calculate the confidence value, the confidence interval is presented with the average alongside the confidence value with a plusminus sign ( ±) in between .
The ‘CONFIDENCE’ function syntax
Here’s the syntax for this function:
=CONFIDENCE(alpha,standard_dev,size)
It has three (3) required arguments:
 Alpha(the significance level which is calculated as 1 – confidence level; a 95% confidence level has a 0.05 significance level)
 Standard_dev(the standard deviation of the data set)
 Size(the population size)
Although the average is not one of the arguments, you have to calculate the average to get the confidence interval. The result from the ‘CONFIDENCE’ function is added to and subtracted from the average .
If the average is 100 and the confidence value is 10, that means the confidence interval is 100 ± 10 or 90 – 110.
If you don’t have the average or mean of your data set, you can use the Excel ‘AVERAGE’ function to find it.
Also, you have to calculate the standard deviation which shows how the individual data points are spread out from the mean.
In Excel, there are two functions you can use to calculate the standard deviation: STDEV.P and STDEVPA . Use the first one only — STDEV.P — since it ignores nonnumeric data.
New Functions
Excel has released 2 new similar functions, the ‘CONFIDENCE.NORM’ and ‘CONFIDENCE.T’ functions with similar arguments.
The ‘CONFIDENCE’ function is still usable for backward compatibility. However, it’s recommended to learn the 2 new functions since Excel claimed these functions have improved accuracy.
How to use the function
Using the ‘CONFIDENCE’ function is easy and straightforward. All you have to do is supply the parameters with their appropriate cell references and/or values .
Let’s use the data set shown below:
Here, we’ll be solving for the confidence interval of the time it takes for a certain fastfood company to deliver your order.
Assuming you have the same order for all 10 instances, the delivery takes 55.4 minutes on average with a standard deviation of 8.499.
In addition, the fastfood company committed a 95% confidence value.
Using the ‘CONFIDENCE’ syntax, we’ll arrive at the following equation:
=CONFIDENCE(0.05,8.499,10) or =CONFIDENCE(E4,E6,E7)
 Alpha: 0.05 ( the significance level which is calculated as 1 – confidence level; a 95% confidence level has a 0.05 significance level)
 Standard_dev: 8.499(the standard deviation of the data set)
 Size: 10(the population size)
From that, we get 5.27 (round up to 2 decimals) as the confidence value.
The confidence interval, which is added to and subtracted from the mean, is 50.13 – 60.67 or 55.4 ± 5.27 .
In this course:
Instead of using a calculator, use Microsoft Excel to do the math!
You can enter simple formulas to add, divide, multiply, and subtract two or more numeric values. Or use the AutoSum feature to quickly total a series of values without entering them manually in a formula. After you create a formula, you can copy it into adjacent cells — no need to create the same formula over and over again.
Subtract in Excel
Multiply in Excel
Divide in Excel
Learn more about simple formulas
All formula entries begin with an equal sign ( =). For simple formulas, simply type the equal sign followed by the numeric values that you want to calculate and the math operators that you want to use — the plus sign ( +) to add, the minus sign ( –) to subtract, the asterisk ( *) to multiply, and the forward slash ( /) to divide. Then, press ENTER, and Excel instantly calculates and displays the result of the formula.
For example, when you type =12.99+16.99 in cell C5 and press ENTER, Excel calculates the result and displays 29.98 in that cell.
The formula that you enter in a cell remains visible in the formula bar, and you can see it whenever that cell is selected.
Important: Although there is a SUM function, there is no SUBTRACT function. Instead, use the minus () operator in a formula; for example, =83+24+12. Or, you can use a minus sign to convert a number to its negative value in the SUM function; for example, the formula =SUM(12,5,3,8,4) uses the SUM function to add 12, 5, subtract 3, add 8, and subtract 4, in that order.
Use AutoSum
The easiest way to add a SUM formula to your worksheet is to use AutoSum. Select an empty cell directly above or below the range that you want to sum, and on the Home or Formula tabs of the ribbon, click AutoSum > Sum. AutoSum will automatically sense the range to be summed and build the formula for you. This also works horizontally if you select a cell to the left or right of the range that you need to sum.
Note: AutoSum does not work on noncontiguous ranges.
In the figure above, the AutoSum feature is seen to automatically detect cells B2:B5 as the range to sum. All you need to do is press ENTER to confirm it. If you need to add/exclude more cells, you can hold the Shift Key + the arrow key of your choice until your selection matches what you want. Then press Enter to complete the task.
Intellisense function guide: the SUM(number1,[number2], …) floating tag beneath the function is its Intellisense guide. If you click the SUM or function name, it will change o a blue hyperlink to the Help topic for that function. If you click the individual function elements, their representative pieces in the formula will be highlighted. In this case, only B2:B5 would be highlighted, since there is only one number reference in this formula. The Intellisense tag will appear for any function.
Learn more in the article on the SUM function.
Avoid rewriting the same formula
After you create a formula, you can copy it to other cells — no need to rewrite the same formula. You can either copy the formula, or use the fill handle to copy the formula to adjacent cells.
For example, when you copy the formula in cell B6 to C6, the formula in that cell automatically changes to update to cell references in column C.
When you copy the formula, ensure that the cell references are correct. Cell references may change if they have relative references. For more information, see Copy and paste a formula to another cell or worksheet.
Written by cofounder Kasper Langmann, Microsoft Office Specialist.
The ‘CONFIDENCE’ function is one of Excel’s oldest statistical functions .
The ‘CONFIDENCE’ function calculates the confidence value for the confidence interval of a data set . A confidence interval is a defined range of values that might contain the true mean of a data set .
In this tutorial, you’ll get to know more about the ‘CONFIDENCE’ function, look under its hood, and figure out how to make it work.
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.
Table of Content
Get your FREE exercise file
Before you start:
Throughout this guide, you need a data set to practice.
I’ve included one for you (for free).
Download it right below!
Download the FREE Exercise File
What is the ‘CONFIDENCE’ function?
The ‘CONFIDENCE’ function is an Excel statistical function that returns the confidence value using the normal distribution .
In turn, the confidence value is used to calculate the confidence interval (or CI) of the true mean (or average) of a population.
It’s a way to represent the uncertainty of your data in a scientific way . It gives the reader or user a socalled ‘margin of error’.
Most of the time, people use 95% as the confidence level. Meaning, out of 100 repeated experiments, the true mean is found in 95 of them.
However, a 95% confidence level is not a standard. You can choose your own confidence level, although, people commonly use 90% – 99% to well… instill confidence. 😊
After you calculate the confidence value, the confidence interval is presented with the average alongside the confidence value with a plusminus sign ( ±) in between .
The ‘CONFIDENCE’ function syntax
Here’s the syntax for this function:
=CONFIDENCE(alpha,standard_dev,size)
It has three (3) required arguments:
 Alpha(the significance level which is calculated as 1 – confidence level; a 95% confidence level has a 0.05 significance level)
 Standard_dev(the standard deviation of the data set)
 Size(the population size)
Although the average is not one of the arguments, you have to calculate the average to get the confidence interval. The result from the ‘CONFIDENCE’ function is added to and subtracted from the average .
If the average is 100 and the confidence value is 10, that means the confidence interval is 100 ± 10 or 90 – 110.
If you don’t have the average or mean of your data set, you can use the Excel ‘AVERAGE’ function to find it.
Also, you have to calculate the standard deviation which shows how the individual data points are spread out from the mean.
In Excel, there are two functions you can use to calculate the standard deviation: STDEV.P and STDEVPA . Use the first one only — STDEV.P — since it ignores nonnumeric data.
New Functions
Excel has released 2 new similar functions, the ‘CONFIDENCE.NORM’ and ‘CONFIDENCE.T’ functions with similar arguments.
The ‘CONFIDENCE’ function is still usable for backward compatibility. However, it’s recommended to learn the 2 new functions since Excel claimed these functions have improved accuracy.
How to use the function
Using the ‘CONFIDENCE’ function is easy and straightforward. All you have to do is supply the parameters with their appropriate cell references and/or values .
Let’s use the data set shown below:
Here, we’ll be solving for the confidence interval of the time it takes for a certain fastfood company to deliver your order.
Assuming you have the same order for all 10 instances, the delivery takes 55.4 minutes on average with a standard deviation of 8.499.
In addition, the fastfood company committed a 95% confidence value.
Using the ‘CONFIDENCE’ syntax, we’ll arrive at the following equation:
=CONFIDENCE(0.05,8.499,10) or =CONFIDENCE(E4,E6,E7)
 Alpha: 0.05 ( the significance level which is calculated as 1 – confidence level; a 95% confidence level has a 0.05 significance level)
 Standard_dev: 8.499(the standard deviation of the data set)
 Size: 10(the population size)
From that, we get 5.27 (round up to 2 decimals) as the confidence value.
The confidence interval, which is added to and subtracted from the mean, is 50.13 – 60.67 or 55.4 ± 5.27 .
Using Microsoft Excel you can quickly compute the mean, standard deviation, and standard deviation of the mean (SDOM) of a set of data, and you can rapidly perform all sorts of error calculations.
Computing Means and Standard Deviations
A common task is to average several data points, and compute the standard deviation of the mean (SDOM) to estimate the uncertainty of the measurement. The figure shows an example of performing this calculation. The values to average are placed in column B, rows 2 through 5. In cell B8 a formula is typed to compute the mean (average). The formula is shown immediately to the right. It is =AVERAGE(B4..B8). All formulas in Excel begin with an equal sign, which must be the first character in the formula. The argument to the AVERAGE function is a range of cells. Of course, for this example you can calculate the mean by inspection, but it’s reassuring that Excel gets the right answer.
The next cell down has the formula for computing the standard deviation. Does Excel use n or n1 to compute the standard deviation? Is the computation of the SDOM done correctly?
Repetitive Calculations
Even better than computing something once using Excel is reusing the calculation many times. If you have a great many sets of 5 numbers for which you need to know the statistics, you can just type the new values over the 5 old values and write down the resulting average and SDOM. Alternatively, you can type each set of values in its own column and copy the formulas for average, standard deviation, and SDOM from column B to all the other columns.
Select the cells whose formulas you wish to copy (B8..B10). Then place the cursor at the lower right corner of cell B10. It will turn from an arrow to the solid black cross shown. Drag to the right through two columns and updated formulas will be written into these two columns. The average of each successive column has indeed risen by 1, as it should. Note also that the formula in D8, as shown in E8, now involves the values in column D, not column B as in the source. Excel automatically updates the references. Cell references that are modified when copied in this way are called relative references. Why are the standard deviation and SDOM values the same?
Plotting Data as You Go
Excel is also very useful for entering and plotting data as you take it. Making a crude plot as you go is a very useful way to see whether the data make sense, where you might need to take more points, and when a data point doesn’t seem to be following the trend. Can you spot the errant point in the data sheet and plot below?
Here’s how to put together such a sheet.

Label the data sheet with an informative title that explains what data you are taking and when.
Enter the titles for the columns you need. You can adjust their formatting with the icons in the toolbar, if you like. Although the raw data come logically before their mean and SDOM, I find it more convenient to put the raw data to the right, leaving room for the results you really care about.
Set up the formulas for the first row. In this case, the raw data are separate trial values for the range (in centimeters). To get the best estimate of the true range, we average using Excel’s AVERAGE function. In cell C6 use the formula =AVERAGE($H6..$L6). The dollar signs make the column references absolute, so they won’t change when you copy.
Copy this formula to cell D6 either using Copy and Paste or by dragging the box at the lower right corner of cell C6. Then edit the formula, changing the word AVERAGE to STDEV, then appending the text /SQRT(5) so the whole formula reads =STDEV($H6..$L6)/SQRT(5). This computes the standard deviation of the mean (SDOM).
Now you can select the two formulas you have created and copy them down as many rows as you like. As you type in your experimental values in the Raw Data table, the appropriate averages and uncertainties are immediately recalculated.
To set up the graph, select cells B6 through C15 (or you can even select more rows, if you expect to take data at more than 10 launch angles). Then click the graph tool button to use the Graph Wizard to set up the graph. This kind of graph is called an XY (Scatter) graph. Answer the questions and let Excel put the graph on the page. As you type in more data values, the graph automatically updates. Very nice!
An uncertainty chart, or a fan chart as they are known, is a way to display historical data along with a prediction of future values. They are often used to indicate inflation or exchange rate predictions, but can be used to display any data with an uncertain future value. They look like a line chart, but the future values fan out to represent the range and probability of future values.
The term “fan chart” was created by the Bank of England who started using this method for displaying inflation in 1997.
The image below shows the finished fan chart which we will be creating in this tutorial.
The data
The layout of the data is one of the most important factors in creating a fan chart. If we get this part wrong the chart will not look how we expect it to.
In our example, I have taken 18 periods of actual results (Cells B2 – S2) and 6 periods of future prediction (Cells T2 – Y2).
I have then set the minimum result for the first 18 periods equal to the actual (Cells B3 – S3). But the minimum for the future periods are purposefully created to be further away from the Result line as each period progresses (Cells T3 – Y3).
The maximum result has no values for the first 18 months (Cells B13 – S13). For future periods the values diverge from the Result line by the same amount as the minimum, but in the opposite direction (Cells T11 – Y11).
Between the minimum and maximum, I have created various increments to represent the fans in the uncertainty of the future prediction. In our example, there are 9 different fans, but you could create any number. Though, if you wish to have a single color in the middle it is easier to use an odd number, rather than set the inner colors to be the same.
It is not essential for the fans to be the same size as each other and depending on your requirements you might decide specifically to have different sizes. But, it is essential that the Minimum plus the increments equal the Maximum.
To create the fans, type the following into Cell T4:
This formula is based on Maximum minus minimum, then divided by the number of fans. Copy this formula across into Cells U4 to Y4. Then copy Cells T4 – Y4 down to Cells T12 – Y12.
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other timesaving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
Create the fan chart
This chart is actually a combination of two charts; a line chart for the result, which is on top on top of a stacked area chart for the fans.
Select cells A1 – Y12 (we can ignore the Maximum, as it was purely used to calculate the size of the fans.
Click: Insert > Charts > Area > Stacked Area (this tutorial is created using Excel 2016 the other versions of Excel may vary slightly).
A chart similar to the following should appear:
Next, rightclick the bottom data series (showing as blue – Series 1 in the screenshot above). From the menu select “Change Series Chart type . . .”
Change to chart type to a line chart.
All the hard work is now done, from here on out it is all formatting.
Now, change the fill of Series 2 (the orange colored area) to be transparent. Rightclick this area and select the Fill paint can, then click No Fill.
Next, select the legend (the box with all the Series listed), press Delete to remove the box.
Rightclick each of the fans and change the Fill. We want to select colors for each fan which gives a lighter color on the outside fans and darker colors on the inside fans, or if we could use increasing levels of transparency – which also has the advantage of displaying the gridlines, should you want them.
Format the Result line in a similar way.
Rightclick on the bottom axis. Within the Format Axis window select Axis Options > Axis Position: – On tick marks
Now we have a beautiful Uncertainty Chart (Fan Chart).
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
 Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
 Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
 Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
 Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: