قالب وردپرس درنا توس
Home / Tips and Tricks / How to Get Microsoft Excel to Calculate Uncertainty

How to Get Microsoft Excel to Calculate Uncertainty



  Excel Logo

The accuracy of most statistical data is questionable – even when procedures are followed and efficient devices are used for testing. In Excel, you can calculate the uncertainty based on the standard deviation of your sample.

There are statistical formulas in Excel that you can use to calculate the uncertainty. This article calculates the arithmetic mean, standard deviation, and standard error. We will also examine how we can represent this uncertainty in a chart in Excel.

We will use the following sample data with these formulas:

 Sample Uncertainty Calculation Data

This data shows five persons who have made a measurement or measurement. With five different readings, we have uncertainty about the actual value.

Arithmetic mean

If you have uncertainty over a range of different values, averaging (arithmetic mean) can serve as a reasonable estimate.

This is easy to do in Excel with the AVERAGE function.

In the above sample data, you can use the following formula:

  = AVERAGE (B2: B6) 

  Average of a series of measurements

Standard deviation of values ​​

The standard deviation functions show how widespread your data is from one central point (the average mean we calculated in the last section).

Excel has several different standard deviation functions for different purposes. The two most important are STDEV.P and STDEV.S.

Each of them calculates the standard deviation. The difference between the two is that STDEV.P is based on specifying the entire population of values. STDEV.S works with a smaller sample of this data population.

In this example, we use all five values ​​of the record, so we will work with STDEV.P.

This function works in the same way as AVERAGE. You can use the following formula for this example of data:

  = STDEV.P (B2: B6) 

  Standard deviation of a set of values ​​using STDEV.P

The result of these five different values ​​are values 0.16. This number indicates how different each measurement is typically from the average value.

Calculating the Standard Error

Once the standard deviation has been 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 calculates the standard error in our sample data.

  = D5 / SQRT (COUNT (B2: B6)) 

  Calculate the standard error ]

Using error bars to represent uncertainty in charts

Excel makes it wonderfully easy to use the standard deviations or Represent uncertainty margins in charts. We can do this by adding error bars.

Below is a bar graph from an example dataset showing a population measured over five years.

 Column chart with population data

With the click on Design> Add Diagram Element.

Then select from the different error types available.

 Select an error bar type for your chart.

You may receive a standard error or a standard deviation amount for all values, as we calculated earlier in this article. You can also see a percentage error change. The default value is 5%.

In this example, the percentage was displayed.

 Percent Margin Error Bar

There are a few other options for customizing your error bars.

Double-click an error bar in the chart to open the Format Error Bars panel. Select the category "Error Bars Options" 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 generated using a statistical formula. [19659003]   Format error bars to further customize them.

Excel is an ideal tool for statistical analysis and reporting. There are many ways to calculate the uncertainty so you get what you need.




Source link