Adnan Masruf
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.
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,
=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 Graph Uncertainty in Excel (2 Easy Ways)
Similar Readings
- Create Fibonacci Calculator in Excel (With Easy Steps)
- How to Hard Code in Excel (4 Easy Ways)
- Create a Fibonacci Pivot Point Calculator in Excel
- Make Fishbone Diagram in Excel (with Easy Steps)
- How to Make Sankey Diagram in Excel (with Detailed Steps)
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.
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 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.
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.
- 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 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
- Make an Ogive Graph in Excel (with Easy Steps)
- How to Create Venn Diagram from Pivot Table in Excel (2 Ways)
- Create a Questionnaire in Excel (2 Easy Ways)
- How to Set Intervals on Excel Charts (2 Suitable Examples)
- If a Value Lies Between Two Numbers Then Return Expected Output in Excel
- Descriptive Statistics – Input Range Contains Non-Numeric Data
- How to Get Summary Statistics in Excel (7 Easy Methods)
