How to Calculate Uncertainty in Excel (3 Effective Ways)

Today, we will show how to calculate uncertainty in Excel. Uncertainty is a part of any random dataset. It is often referred to the doubt surrounding the accuracy of the data. It is the deviation from the mean of the dataset. We can calculate the uncertainty of a dataset by using the Standard Deviation. It is very important because it determines how much one could trust the data and the extent of the practical implication of the data.


Download Practice Workbook

You can download the practice workbook here.


3 Ways to Calculate Uncertainty in Excel

In this article, we will discuss how to calculate uncertainty in Excel in 3 different ways. Firstly, we will use the manual uncertainty formula to calculate uncertainty. Secondly, we will use the STDEV.S function to calculate the standard deviation and then determine the uncertainty. Finally, we will calculate the Absolute and Percentage uncertainty.


1. Using Uncertainty Formula

In this method, we will insert the manual uncertainty formula to calculate the uncertainty. We will use the SUM, COUNTA, AVERAGE, and SQRT functions to set up the uncertainty equation. Here, we have a set of readings of a certain parameter. We are going to determine the uncertainty of the readings or in other words errors of the readings.

how to calculate uncertainty in excel

Steps:

  • To begin with, choose the C12 cell and enter,
=AVERAGE(B5:B10)
  • Then, hit Enter.
  • As a result, we will get the average of the readings.

averaging values to show how to calculate uncertainty in excel

  • After that, click on the C13 cell and type,
=COUNTA(B5:B10)
  • Then, press Enter.
  • Consequently, we will have the number of readings.

counting number of values to show how to calculate uncertainty in excel

  • Now, select the C5 cell and insert,
=B5-$C$12
  • Click on Enter.
  • As a result, we will subtract the average value from the reading value.

calculating difference between average and the value to show how to calculate uncertainty in excel

  • Then, lower the cursor down to Autofill.

  • Thereafter, click on the D5 cell and type,
=C5^2
  • Press the Enter button.
  • Consequently, we will get the square of the previously obtained value.

  • Next, move the cursor to the last cell to autofill.

using uncertainty formula to show how to calculate uncertainty in excel

  • Finally, choose the F5 cell and enter the following formula,
=SQRT(SUM(D5:D10)/(C13*(C13-1)))
  • Next, press Enter.
  • Consequently, we will get the uncertainty value for the dataset.

Thus, we calculate uncertainty in Excel.

Read More: How to Graph Uncertainty in Excel (2 Easy Ways)


Similar Readings


2. Using STDEV.S Function

In this instance, we will get the Standard Deviation of the readings. Then, we will use it to get the uncertainty. Here, we have the readings of concentration of a certain chemical for different dates. We will find out the uncertainty of the readings.

how to calculate uncertainty in excel

Steps:

  • Firstly, choose the C12 cell and enter,
=STDEV.S(C5:C10)
  • Then, press Enter.
  • Consequently, we will get the Standard Deviation of the dataset.

  • Next, click on the C13 cell and insert,
=C12/SQRT(COUNTA(C5:C10))
  • Hit Enter.
  • As a result, we will get the uncertainty of the dataset.

using stdev.s function to show how to calculate uncertainty in excel

So, we will have the uncertainty value in Excel.

Read More: How to Create Uncertainty Budget in Excel (with Easy Steps)


3. Calculating Absolute and Percentage Uncertainty

In the final method, we will calculate the absolute and percentage uncertainty of the dataset. In this case, we have the readings of concentration of a certain chemical. There are 5 readings, and each reading has been taken three times with each test time recorded. Here, we will calculate the absolute and percentage uncertainty of the test times.

how to calculate uncertainty in excel

Steps:

  • To start with, click on the F5 cell and type the following,
=AVERAGE(C5:E5)
  • Press the Enter buttton.
  • As a result, we will get the average of the 3 test times.

  • Now, lower the cursor to autofill the cells.

  • Next, choose the G5 cell and enter,
=E5-D5/2
  • Then, hit the Enter button.
  • Consequently, we will have the absolute uncertainty of the test times.

calculating absolute uncertainty to show how to calculate uncertainty in excel

  • Now, move the cursor down to the last cell to autofill.

  • Finally, select the H5 cell and enter the following formula,
=G5/F5
  • Next, press Enter.
  • As a result, we will get the percentage uncertainty of the test times.

calculating percentage uncertainty to show how to calculate uncertainty in excel

  • Finally, slide the cursor down to the last cell to autofill.

This is how we will calculate uncertainty in Excel.

Read More: How to Calculate Root Mean Square Error in Excel


Conclusion

In this article, we have discussed 3 ways to calculate the uncertainty of a dataset in Excel. These will allow users to determine the value of possible error in a dataset and calibrate their actions accordingly. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo