How to Find Uncertainty in Google Sheets [Complete Guide]

In this article, we will learn how to find uncertainty in Google Sheets. In statistics, uncertainty is an important concept for data analysis and decision-making, as it reflects the degree of confidence in the results of a calculation or measurement.

In Google Sheets, there are several ways to find and quantify uncertainty, depending on the type of data and the purpose of the analysis. This guide will show you how to find uncertainty in Google Sheets using built-in functions and maybe some custom formulas. We will cover basic concepts of uncertainty, I will show you some general use cases such as error propagation and confidence intervals, and demonstrate how to apply them to different types of data, such as measurements, estimates, and forecasts.

By the end of this guide, you will have the knowledge and skills to find and interpret uncertainty in your own data sets, and make more informed decisions based on your results.

Importance of Finding Uncertainty in Google Sheets

There are various purposes for those we may need to find out the uncertainty in data. for data analysis, uncertainty is an inherent part of any data set and it’s important to understand how it affects the results of your calculations and analysis. By knowing how to find and quantify uncertainty in Google Sheets, you can make more informed decisions based on your data and communicate the level of confidence in your results to others, similar we have error propagation, when you perform calculations on uncertain data, the uncertainty in the input data will propagate through the calculations and affect the uncertainty in the final results.

Knowing how to properly propagate uncertainty in Google Sheets will help you get more accurate results. There is also a thing called confidence intervals, by finding uncertainty, you can also calculate confidence intervals, which is a range of values that is likely to contain the true value with a certain level of confidence.

So, these are some reasons we need to learn how to find uncertainty in Google Sheets.

How to Find Uncertainty in Google Sheets

We have so many built-in functions that can be used in various ways to find uncertainties in Google Sheets. But they all are not the same, and have different main purposes, depending on your problem you can use any of these to find uncertainty. So, I will show you how to use all these functions, and I will show you an example of each finding uncertainty for a sample data set.

How to Find Uncertainty in Google Sheets – Using STDEV

Although STDEV is standard deviation and is used to calculate the standard deviation based on a sample. But the uncertainty is also a part of it so we can use this function to get the level of uncertainty in a given data set.

The syntax is very simple.

STDEV(value1, [value2, …])

• value1 – The first value or range of the sample.
• value2 – [OPTIONAL] – Additional values or ranges to include in the sample.

We simply need to pass the data set inside the function, and we will get the uncertainty of the data

Step 1

Have some sample data.

Step 2

Write the STDEV function in the resultant cell in your file.

Step 3

Pass the entire range or cell as the input, if you have more values, you can add up to 30 optional values separated with commas.

Step 4

Press Enter key and you will get a numeric value which can be said as the uncertainty of the data.

How to Find Uncertainty in Google Sheets – Using STDEVP

Just like STDEV, we also have STDEVP for the population. In this section, we will learn how to find uncertainty in Google Sheets using the STDEVP function. This is also a standard deviation function but it can also be used to find out the uncertainty of the given data.

The syntax is almost the same, the only difference between these two functions is your data, when you have sample data you can use STDEV, and for population data, you can use STDEVP.

Step 1

Write down the STDEVP function in a cell in your Google Sheets file

Step 2

Pass the mandatory parameter, and then pass optional values as well if you need to.

Step 3

Press the Enter key, and you will get a value that can be said as the uncertainty of the dataset for the population data.

How to Find Uncertainty in Google Sheets – Using PROB Function

You must have heard studied probability and statistics in your college. It may sound boring to you but it’s very interesting and nowadays being used in future technologies like big data and data science. We can use a simple prob function in Google Sheets to find out the probability of a given data set. This function has some limitations when taking input so be focused and careful when passing your data.

Since we are seeing this function for the first time at office Demy. Let’s talk about it a little more.

Syntax

PROB(data, probabilities, low_limit, [high_limit])

The argument inside square brackets is optional.

• data: Array or data range containing the data for which you want to calculate the probability.
• probabilities – data range or direct values for the probabilities of the data
• Note: As per the mathematic rule, the values inside the probability argument must be greater than 0 and must not be greater than 1.
• low_limit: The lower limit of the data point we want to calculate the probability for.
• high_limit: It’s optional. if not passed, the function considers it as the value of low limit – It’s the upper limit on the data values for which we want to calculate the probability.

Note: The number of values in the data argument and the probabilities argument must be the same.

Below are sample usage of this function

PROB({1,2,3,4},{0.25,0.25,0.25,0.25},3)

PROB(A2:A100,B2:B100,C2,C3)

Step 1

Here I have some basic sample data for this example.

Step 2

Keeping in mind the rules of the prob function, we will pass values inside the function.

Step 3

This is how we get the probability for our dataset.

To find uncertainty we have a general meth formula. We can use it inside Google Sheets to find out the uncertainty in Google Sheets

How to Find Uncertainty in Google Sheets – Uncertainty Custom Formula

In this section, we will learn how to find uncertainty in Google Sheets using a general statistics formula. We will be needing some more built-in functions of Google Sheets to be used in this custom formula so let’s get started with this section and learn how to find uncertainty in Google Sheets.

The formula for uncertainty

Uncertainty (u) = √ [∑ (xi – μ)2 / (n * (n – 1))]

here,

• xi = ith reading in the data set
• μ = Mean of the data set
• n = Number of readings in the data set

Problem Statement

To understand the uncertainty, first, we need to understand the problem statement. So, assume we have a running race of the kids at the picnic point, and this race was recorded into 5 different stopwatches. In the end, we had a slightly different result in each stopwatch that has a fraction of the difference in time. Now we must calculate the uncertainty of the timing based on the above data and timing with a 68% confidence level.

Step 1

Here we got the first argument, the ith reading in the data set which is 5 because we have got 5 different values in every stopwatch.

Step 2

Now we need to find the mean, for this, we will sum all the reading values and divide them by the number of readings means 5.

Step 3

Now, we need to calculate the standard deviations of each reading. For this we will use Reading – Mean formula for every reading

Step 4

Now we need to calculate the square of the deviations of each reading. For this, we can simply multiply each reading by itself.

Step 5

Now we have everything to put in our uncertainty formula.

Uncertainty is calculated using the formula given below

Uncertainty (u) = √ [∑ (xi – μ)2 / (n * (n-1))]

Step 6

Below you can see the final formula and the result of the readings we got from it, this is how you can find uncertainty in Google Sheets.

Frequently Asked Questions

How to find uncertainty in Google Sheets?

To calculate uncertainty in Google Sheets, you can use built-in functions such as STDEV and STDEVP to determine the standard deviation of a set of data. You can also use the uncertainty formula of math and create it like a custom formula in Google Sheets using some other functions as well to calculate uncertainty.

Can we add error bars to a graph in Google Sheets?

Yes, you can add error bars to a graph in Google Sheets by selecting the data series you want to add error bars to, then going to the Chart Editor and selecting the “Customize” tab. From there, you can select “Any Series” and then “Error bars” to customize the error bars for your graph.

Is there a built-in uncertainty function in Google Sheets?

Unfortunately, no, there is no built-in uncertainty function in Google Sheets, but we have some similar functions like PROB to find probability, have errors bars functionalities inside charts, and some functions like STDEV, STDEVP, CONFIDENT, and GOOGLEFIANANCE to use for finding the level of uncertainty in a data set.

Conclusion

This was everything about how to find uncertainty in Google Sheets, we learned how to use built-in functions to find uncertainty, and then I showed you a typical mathematics formula to find a very nearing value for the uncertainty in a data set.

I hope you like the above article, and that you have learned something new from it. I will see you soon with another useful guide, till then take care and keep learning with Office Demy.