How to Calculate a Tolerance Interval in Excel

This guide will explain how to calculate a tolerance interval in Excel.

We can use the tolerance interval to determine what range covers a fixed proportion of the population.

The tolerance interval is a type of statistical interval within which, at a certain confidence level, a particular proportion of a sampled population will belong.

Let’s take a look at a simple use case for the tolerance interval and how it can help you understand your dataset.

For example, given a population of customers aged 18 to 75, you may want to know the interval where 50% of the population will fall, given a level of confidence of 95%.

Retrieving the tolerance interval requires you to use a few statistical methods. We will need to use built-in statistical methods such as AVERAGE and STDEV.S and custom formulas.

Now that we know when to calculate the tolerance interval, let’s learn how to use it and work on an actual sample spreadsheet.

 

 

A Real Example of Calculating the Tolerance Interval in Excel

The following section provides an example of how to calculate the tolerance interval. We will explain the formulas and tools used in this example.

First, let’s take a look at our example’s sample population.

We have a sample population of 50 values. We want to find a range of values where 90% of our values will fall, given a confidence level of 95%.

find interval with 90% of the population at a confidence level of 95%

 

Before we calculate the tolerance interval’s lower and upper limits, we must find several statistical parameters first.

We’ll use the COUNT and AVERAGE functions to find the sample size and sample mean.

tolerance interval in Excel

 

Next, we’ll use the STDEV.S function to find our sample’s standard deviation. We’ll also need to find the Z-critical value of our given confidence level.

We can find the Z-critical value using the following formula:

=ABS(NORM.INV((1-D3)/2;0;1))

We must also calculate the chi-squared critical value given our sample size and the proportion we want to find in our interval.

You can use this formula to find the chi-squared critical value:

=CHISQ.INV(1-D2;D5-1)

Once we’ve found all these statistical parameters, we can now find the tolerance interval.

We can find the lower limit of this interval using the following formula:

=D6-D7*D8*SQRT(((D5-1)*(1+1/D5))/D9)

We can find the upper limit by replacing the first operation from subtraction to addition:

=D6+D7*D8*SQRT(((D5-1)*(1+1/D5))/D9)

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 finding the tolerance interval yourself, head over to the next section to read our step-by-step breakdown on how to do it!

 

 

How to Calculate a Tolerance Interval in Excel

This section will guide you through each step needed to calculate a tolerance interval in Excel. You’ll learn how to use a combination of custom formulas and built-in statistical functions to find this interval.

Follow these steps to calculate the tolerance interval in Excel:

  1. First, we must enter two key values into our spreadsheet. We must identify the proportion of the population we want to find in our interval and the confidence level we require.
    provide proportion for interval and confidence levelIn this example, we are looking for an interval with 90% of the population at a confidence level of 95%.
  2. We can determine the sample size of our dataset using the COUNT function.
    get sample size of dataset
  3. We’ll use the AVERAGE function to find the mean of our dataset.
    get sample mean for tolerance interval in Excel
  4. Next, we’ll use the STDEV.S function to return the sample standard deviation of our dataset.
    use sample standard deviation
  5. If we want to find our tolerance interval, we’ll need to find the z critical value for our desired confidence level. We’ll use the formula =ABS(NORM.INV((1-D3)/2),0,1)) to determine this value.
    find z critical value
  6. We can find the chi-square critical value using the formula =CHISQ.INV(1-D2,D5-1).
    find chi-square critical value in Excel
  7. Use the formula =D6-D7*D8*SQRT(((D5-1)*(1+1/D5))/D9) to find the lower limit of our tolerance interval.
    create lower limit for tolerance interval in Excel
  8. Use the formula =D6+D7*D8*SQRT(((D5-1)*(1+1/D5))/D9) to return the upper limit of our tolerance interval.
    upper limit of tolerance interval in Excel

These are all the steps you need to calculate a tolerance interval in Excel.

 

 

Frequently Asked Questions (FAQ)

  1. What is the difference between the confidence interval and tolerance interval?
    Both of these statistical methods are ways to estimate an interval or range of values most likely to contain some statistical property.

    Confidence intervals are meant to describe where a specific parameter will be. For example, we can find the confidence interval of a population’s mean.

    The tolerance interval, on the other hand, returns a range where a certain percentage of your population is most likely to fall.

 

 

This step-by-step guide should provide you with all the information you need to start calculating a tolerance interval in Excel.

You can use tolerance intervals to determine the range where a specified proportion of a sample population will be.

The tolerance interval is just one example of the many statistical methods you can apply in your spreadsheets. 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.

 

 

 

3 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like