Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack provides the following functions.
TOLERANCE_LOWER(R1, p, α, type) = lower limit of the 100p% tolerance interval based on the sample in R1, confidence level 1 – α.
If type ≥ 0 then it is assumed that the data in R1 is normally distributed and so the value m-k*s is returned where m = the mean of the data in R1, s = the standard deviation of R1, n = the number of elements in R1 and k = TOLERANCE_NORM(n, p, α, type).
If type = -1, then the lower limit of the one-sided non-parametric tolerance interval is returned, while if type = -2 then the lower limit of the two-sided non-parametric tolerance interval is returned.
TOLERANCE_UPPER(R1, p, α, type) = upper limit of the 100p% tolerance interval based on the sample in R1, confidence level 1 – α.
If type ≥ 0 then it is assumed that the data in R1 is normally distributed and so the value m+k*s is returned where m = the mean of the data in R1, s = the standard deviation of R1, n = the number of elements in R1 and k = TOLERANCE_NORM(n, p, α, type).
If type = -1, then the upper limit of the one-sided non-parametric tolerance interval is returned, while if type = -2 then the upper limit of the two-sided non-parametric tolerance interval is returned.
TOLERANCE_SIZE(p, α, tails) = the minimum sample size required for a tails-sided non-parametric 100p% tolerance interval with confidence level 1–α; tails = 1 or 2 (default)
The default for p is .90, the default for α is .05 and the default for type is 2. For the two-sided interval, TOLERANCE_SIZE is valid for values of p up to about 99.999%, much higher for the one-sided interval.
Observations
The values in Figure 2 of Non-parametric Tolerance Intervals can be computed using these worksheet functions. E.g. cell O13 can be computed by the worksheet formula =TOLERANCE_UPPER(B3:I17,O4,O5,1), while cell T18 can be computed by the formula =TOLERANCE_LOWER(B3:I17,T4,T5,-2).
The minimum sample size for the one-sided non-parametric tolerance interval with p = .90 and α = .01 is 44, as calculated by the formula =TOLERANCE_SIZE(.9, .01, 1). The minimum sample size for the two-sided interval is 64 as calculated by =TOLERANCE_SIZE(.9, .01, 2).
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
Reference
JMP (2020) Tolerance intervals. SAS
https://www.jmp.com/support/help/en/15.1/index.shtml#page/jmp/tolerance-intervals-2.shtml#
Hello,
When I put type=-1 or -2, the TOLERANCE_UPPER returns a !VALUE error, I suspect this is because it uses k-value that comes from TOLERANCE_NORM function that does not accept negative values for type, as described here
https://real-statistics.com/sampling-distributions/tolerance-interval/
Could you please help?
Hello Stathis,
I just checked and TOLERANCE_UPPER is returning a numeric value for the data shown on the webpage when type = -1 or -2.
What do you see when you enter the formula =VER() in any cell?
If you aren’t using the example data, can you email me an Excel file with your data so that I can try to figure out what is ging wrong?
Charles
Thank you Charles,
I figured it out, it was a sample size problem, I was trying to perform =TOLERANCE_UPPER(A2:A61,0.99,0.05,-1) while the sample size was 60. When I make it =TOLERANCE_UPPER(A2:A61,0.95,0.05,-1), it gave me correct answer.
Thanks again,
Stathis
Great set of tools, Dr. Zaiontz! Would you happen to have a function for a tolerance interval for a rare event (perhaps a Poisson tolerance interval)? I currently use R and SAS but a non-stats colleague would like an Excel solution.
Best,
Jesse
Jesse,
Thank you for your kind words. I appreciate it.
Real Statistics doesn’t support a Poisson tolerance interval. Do you have a reference for this capability that would be helpful for its implementation?
Charles
Does real statistics have a function similar to TOLERANCE_SIZE, but for sample size for tolerance intervals on a normal distribution?
No, but you can probably use the TOLERANCE_NORM to get this sort of result. I haven’t tried it, but I think it should work. See
https://www.real-statistics.com/sampling-distributions/tolerance-interval/
Charles