I wrote a User Defined Function that can calculate the Area Under the Curve using Simpsons Rule, the Trapezoid Rule, Left
Rule, or Right Rule. I plan on using these Numerical Integration methods with my AP Physics students in a lab next year.
It can also calculate a fit equation for Linear, exponential, Ln, and Power functions.
Here is an example
of the results. As you can see, when the fit is way off, the results do not make sense. I was unsure how to proceed with
calculating r^2 values for nonlinear data. Hopefully someone, somewhere will find this uesful.
********
******************** ************************************************************************>Microsoft Excel - Area Under
Curve and Fit Function 6-16-05.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata
(W)indow (H)elp
(A)boutC3D3E3F3G3H3C4D4E4F4G4H4C5D5E5F5G5H5C6D6E6F6G6H6C7D7E7F7G7H7C8D8E8F8G8H8C9D9E9F9G9H9C10D10E10F10G10H10C11D11E11F11G11H11C13D13E13F13G13H13C14D14E14F14G14H14C16D16E16F16G16H16C17D17E17F17G17H17C19D19E19F19G19H19C20D20E20F20G20H20=
ABCDEFGH1 Actual ShapeLinearPolynomialPowerExpLnSine2 True Equation7X + 45X^2 + 3X + 503 X^4.254.12
e^1.34XLN(4.56X)+2.35Sin(x)3Area Under
CurveAnalytical390.002316.67102102.172029263.6548.38-0.804Simpson390.002166.67101616.542031416.4148.389.205Trapezoid390.002168.75102088.182104609.8948.369.006Left372.502043.7588751.081424807.6047.799.687Right407.502293.75115425.272784412.1848.948.328Linear
Fit:
AX +
BA4.00-29.17-10599.34-382252.514.121.329B7.0050.004318.44129486.960.22-0.1110R^21.000.930.720.380.910.0111R^21.000.970.850.610.960.1012Exp
Fit:
A e^BX 13A13.6547.84188.654.124.2312.6914B0.180.250.571.340.040.1615Ln Fit:
A + Ln(B) 16Ln Fit A39.00220.8310992.88265182.315.350.7917Ln Fit B26.93179.3814255.62394281.551.00-0.9418Power Fit
A X^B 19Power Fit A31.12165.69988.903347.115.311.8620Power Fit B0.991.014.505.160.20-0.16Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.