How to Apply Chebyshev’s Theorem in Excel

This guide will explain how to apply Chebyshev’s Theorem in Excel.

This theorem is often used to find the amount of observations expected to be within a certain distance from the mean.

For normal distributions, we know that about 68% of results will fall within a standard deviation from the mean. Furthermore, we know that about 95% of results in a normal distribution will fall within two standard deviations from the mean.

How can we find the ratio of results within a certain range for a dataset that does not follow normal distributions?

Chebyshev’s theorem is a rule that can be applied to all possible data sets. The theorem can help you find the proportion of observations you should find within a certain range.

The theorem itself states that for any given population or sample, the proportion of observations shall be no less than (1 – (1/k2)).

Let’s take a look at an example where we can use Chebyshev’s Theorem to find the number of results in a given range around the mean.

Suppose you have a dataset of 1000 values. The population mean is 455, and the population’s standard deviation is 353.

What is the expected number of results that are at most 1 standard deviation from the mean?

We can use Chebyshev’s Theorem in Excel by converting the equation into a custom Excel formula.

We will only have to substitute k with the number of standard deviations away from the mean your range will cover.

For examples where the start and end of a range are given, we can use Excel functions to determine the standard deviations we’ll need to find the result through Chebyshev’s theorem. 

Now that we know when to apply Chebyshev’s theorem, let’s learn how to use it on an actual sample spreadsheet.

A Real Example of Using Chebyshev’s Theorem in Excel

The following section provides several examples of how to use this function. We will also explain the formulas and tools used in these examples.

First, let’s take a look at a sample problem where we can use Chebyshev’s theorem to find the result.

Suppose you want to find the percentage of results that fall within the values 20 to 60 in a population with a mean of 40 and a standard deviation of 10.

Before we use Chebyshev’s theorem, we must first find the value of k. We can find the difference between both A and B from the mean and divide the result by the standard deviation.

We calculated the distance from the mean through the following formula:

=(LIMIT - MEAN)/ STANDARD_DEVIATION

Since both distances have an absolute value of 2, we can conclude that our k is 2. We’ll use this value of k to find our percentage using Chebyshev’s theorem.

We’ll use the following formula to find the proportion of values within k standard deviation of the mean:

= 1 - (1/B11^2)

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try applying Chebyshev’s theorem yourself, head over to the next section and follow our step-by-step guide on how to do it!

How to Apply Chebyshev’s Theorem in Excel

This section will guide you through each step needed to apply Chebyshev’s theorem in Excel. You’ll learn how to find the value of k given the start and end of a range around the mean. After finding k, we’ll use a formula to find the proportion of data that should fall within k standard deviations from the mean.

Follow these steps to start using Chebyshev’s theorem in Excel:

1. First, create a table that will hold the values we’ll need to find the value of k. In this example, Values A and B indicate the start and end of the range we want to use Chebyshev’s theorem on. We also write down the mean and standard deviation of the population.
   
2. We’ll find the distance of point A from the mean through the formula (B3-B5)/B6. The output of this formula will be the number of standard deviations away the point is from the mean.
   
3. We will use a similar formula to find the distance of B from the mean.
   
4. We can set the value of k to 2 since our range starts and end two standard deviations away from the population mean.
   
5. We’ll use the formula =1-1/B11^2 to return the percentage of values within k standard deviations of the mean.
   
6. Hit the Enter key to evaluate the result. In our example, we were able to find out that about 75% of the population falls within 2 standard deviations of the mean.
   

These are all the steps needed to do apply Chebyshev’s theorem in Excel. 

Frequently Asked Questions (FAQ)

Here are some frequently asked questions about Chebyshev’s theorem in Excel:

1. What are the limitations of Chebyshev’s theorem?
   Users should keep in mind that the theorem only provides the minimum proportion of data that falls within a given number of standard deviations of the mean. Your dataset’s true proportions may be greater than what the theorem states.

This step-by-step guide is a quick introduction to applying Chebyshev’s theorem in Microsoft Excel.

You should now know how to determine the percentage of your data set that falls within k standard deviations of the mean.

Chebyshev’s theorem is just one example of the many statistical use cases you can perform in an Excel spreadsheet. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe