O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.8. Computing Confidence Intervals

Problem

You want to compute confidence intervals along with your curve fits.

Solution

See the following discussion.

Discussion

You can compute confidence intervals for the estimated values from your curve fit, or you can compute confidence intervals for the parameters in the model itself.

Confidence intervals for estimated values

You can readily compute confidence intervals for the values predicted by a regression equation. In this case, you'd report estimated values as y ± y c, where y is the estimated value and each ±y c is the confidence limit corresponding to some degree of confidence (probability), c. To compute confidence limits, we'll utilize the Student's t-distribution , which comes from an area of statistics called small sampling theory . Excel provides support for the t-distribution through several built-in functions. See Recipe 5.6 for more information.

Let's reconsider the example from Recipe 8.6. Figure 8-12 shows the result of a nonlinear curve fit, along with upper and lower confidence limits. Figure 8-11 shows the original data along with the fit parameters. It also shows several other statistics I computed for this example. The ones of interest here are t-value and Confidence Interval, shown in cells M13 and M14.

Before computing these values, you must set up some auxiliary calculations. First you must compute the sum of squared residuals as shown in cell M5. The formula used in this example is =SUMSQ(H5:H41), which is just the sum ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required