Some Equations for Curve Fitting

This appendix describes a number of equation types that can be used for curve fitting. Some of the equation types can be handled by Excel's Trendline utility for charts; these cases are noted below.

Multiple Regression. Multiple regression fits data to a model that defines y as a function of two or more independent x variables. For example, you might want to fit the yield of a biological fermentation product as a function of temperature (7), pressure of C02 gas (P) in the fermenter and fermentation time using data from a series of fermentation experiments with different conditions of temperature, pressure and time.

Since you can't create a chart with three jc-axes (e.g., T, P and t), you can't use Trendline for multiple regression.

Polynomial Regression. Polynomial regression fits data to a power series such as equation A4-2:

Figure A4-1. Polynomial of order 3. The curve follows equation A4-2 with a = 5,b = -\,c = -5 and d= 1.

The Trendline type is Polynomial. The highest-order polynomial that Trendline can use as a fitting function is a regular polynomial of order six, i.e., y = ax6 + bx5 +cx4 + dx + ex2 +fx + g.

LI NEST is not limited to order six, and LI NEST can also fit data using other n n ja -i «

polynomials such as y = ax + bx + cx + dx + e. Exponential Decrease.

0.00

0.08

0.04

0.02

0.08

0.04

0.02

Figure A4-2. Exponential decrease to zero. The curve follows equation A4-3 with a = 0.1 and b = -0.5. The Trendline equation is shown on the chart.

Data with the behavior shown in Figure A4-2 can be fitted by the exponential equation y = aeb* (A4-3)

The sign of b is often negative (as in radioactive decay), giving rise to the decreasing behavior shown in Figure A4-2.

The linearized form of the equation is In y = bx + In a; the Trendline type is Exponential.

Exponential Growth. If the sign of b in equation A4-3 is positive, the curvature is upwards, as in Figure A4-3.

Figure A4-3. Exponential increase. The curve follows equation A4-3 with a = 0.1 and b = 0.5. The Trendline equation is shown on the chart.

Figure A4-3. Exponential increase. The curve follows equation A4-3 with a = 0.1 and b = 0.5. The Trendline equation is shown on the chart.

Exponential Decrease or Increase Between Limits. If the curve decreases exponentially to a nonzero limit, or rises exponentially to a limiting value as in Figure A4-4, the form of the equation is y - aebx + c (A4-4)

Excel's Trendline cannot handle data of this type.

Figure A4-4. Exponential increase to a limit. The curve follows equation A4-4 with a = -\,b = -0.5 and c = 1.

Figure A4-4. Exponential increase to a limit. The curve follows equation A4-4 with a = -\,b = -0.5 and c = 1.

The linearized form of the equation is In (y - c) = bx + In a.

Double Exponential Decay to Zero. The sum of two exponentials (equation A4-5) gives rise to behavior similar to that shown in Figure A4-5. This type of behavior is observed, for example, in the radioactive decay of a mixture of two nuclides with different half-lives, one short-lived and the other relatively longer-lived.

Figure A4-5. Double exponential decay. The curve follows equation A4-5 with a = 1, b = -2, c = 1 and d = -0.2.

Figure A4-5. Double exponential decay. The curve follows equation A4-5 with a = 1, b = -2, c = 1 and d = -0.2.

If the second term is subtracted rather than added, a variety of curve shapes are possible. Figures A4-6 and A4-7 illustrate two of the possible behaviors.

Figure A4-6. Double exponential decay. The curve follows equation A4-5 with a = 1, b = -0.2, c = -2 and d = -2.

Figure A4-7. Double exponential decay. The curve follows equation A4-5 with a = 1, 6 = -2, c = -1 and d = -0.2.

Equation A4-5 is intrinsically nonlinear (cannot be converted into a linear form).

Power. Data with the behavior shown in Figure A4-8 can be fitted by equation A4-6.

x

Figure A4-8. Power curve.

The curve follows equation A4-6 with a = 1.1, b =-0.5. The Trendline equation is shown on the chart.

The linearized form of equation A4-6 is In y = b In x + In a; the Trendline form is Power.

+5 -3

Responses

• karin
How to calculate power curve fit in vba?
9 years ago
• daphne
Why can't a linear trend line determine the total fermentation time?
4 years ago
• Jarno
How to do a curve fit with linest in vba?
1 year ago
• Bosco
How to perform vba macro based on best fit curve on graph?
4 months ago
• phillipp zimmermann
Can we do curve fitting in excel custom equation?
3 months ago