Today, we will show how to calculate uncertainty in Excel. Uncertainty is a part of any random dataset. It is often referred to as 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 can trust the data and the extent of the practical implications of the data.
How to Calculate Uncertainty in Excel: 3 Effective Ways
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.
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.
- After that, click on the C13 cell and type,
=COUNTA(B5:B10)- Then, press Enter.
- Consequently, we will have the number of readings.
- Now, select the C5 cell and insert it,
=B5-$C$12- Click on Enter.
- As a result, we will subtract the average value from the reading value.
- 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.
- 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 Calculate Standard Deviation of y Intercept in Excel
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 the concentration of a certain chemical for different dates. We will find out the uncertainty of the readings.
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.
So, we will have the uncertainty value in Excel.
Read More: How to Calculate Population Standard Deviation in Excel
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 the 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.
Steps:
- To start with, click on the F5 cell and type the following,
=AVERAGE(C5:E5)- Press the Enter button.
- 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.
- 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.
- 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 Standard Deviation with IF Conditions in Excel
Download Practice Workbook
You can download the practice workbook here.
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 errors 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.
