قالب وردپرس درنا توس
Home / Tips and Tricks / How to get Microsoft Excel to calculate uncertainty

How to get Microsoft Excel to calculate uncertainty



  excel logo

There is uncertainty about the accuracy of most statistical data, even when following the following procedures and using efficient equipment to test. Excel allows you to calculate uncertainty based on your sample's standard deviation.

There are statistical formulas in Excel that we can use to calculate uncertainty. And in this article, we will calculate the arithmetic mean, the standard deviation, and the standard error. We will also look at how we can plot this uncertainty on a chart in Excel.

We use the following sample data with these formulas.

 Test data for calculating uncertainty

These data show five people who have made a measurement or reading of some kind. With five different readings, we have uncertainty over what the real value is.

Arithmetic Mean

When you have uncertainty over a number of different values, 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 sample data above.

  = AVERAGE (B2: B6) 

  Mean value of a set of measurements

The standard deviation of the values ​​

The standard deviation functions show the amount of scatter your data is from a central point (the average mean we calculated in the last section) .

Excel has some different standard deviation functions for different purposes. The two most important 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 the fact that you deliver it with the entire population of values. STDEV.S is working on a smaller selection of that population of data.

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

The function works in the same way as AVERAGE.

  = STDEV.P (B2: B6) 

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

The result of these five different values ​​is 0.16. This number tells how different each measurement is usually from the mean.

Calculate the default error

With the calculated standard deviation, we can now find the default error.

The default error is the standard deviation divided [19659000] The formula below will calculate the standard error of our sample data.

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

  Calculate standard error ]

Using error fields to present uncertainty in charts

Excel makes it easy to plot standard deviations or margins of uncertainty on chart. We can do this by adding field bars.

Below we have a column diagram from a sample data series showing a population measured over five years.

 Column diagram showing population data

With

Select a type of graph bar "width =" 326 "height =" 398 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif " onload = "pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);" onerror = "this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);" />

we calculated earlier in this article. You can also display a percentage error change. The default value is 5%.

For this example, we chose to display percentage.

 Error field showing percentage

There are a few more options to explore to customize your error fields.

Double-click an error field on the chart to open the Format Error Slots window, select the "Error field options" option if it is not already selected.

You can then adjust the percentage the distance, the standard deviation or even choose a custom value from a cell that may have been produced with a statistical formula. [19659003]   Format fields for customizing them

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




Source link