EXPONENTIAL FIT

The World Record for the Mile Analysis

Objective: To explore the basic ideas of exponential fitting by using Microsoft Excel to perform a step by step analysis of a data set to find the best fitting exponential curve in the form y = ae^bx.

Click on the appropriate link.

Done the other sections of the lesson.

This is my first section.

DIRECTIONS FOR THOSE WHO HAVE DONE THE OTHER SECTIONS:

  1. Download the World Record spreadsheet.

  2. Use the spreadsheet, as in previous sections, to find the best-fitting curve to the world record times data.
Some EXCEL commands that may be useful are:

LN() - LN(A12) or LN(100). Finds the natural log of a cell.

SUM() - SUM(A12:A21). Finds the sum of the values in cells A12 through A21.

AVERAGE() - AVERAGE(A12:A21). Finds the average of cells A12 through A21.

EXP() - EXP(2) or EXP(A12). Finds e2 or eA12 .

ABS() - ABS(-3) or ABS(A12). Finds the absolute value of a cell.

DIRECTIONS FOR THOSE WHO HAVE NOT DONE THE OTHER LESSONS:
  1. Download the World Record spreadsheet.

  2. Find the natural log of each time data point in column D.

  3. In cells B33 and D33 find the sum of each list and in cells B34 and D34 find the mean. In cell D35, calculate e raised to the mean.

  4. The first guess for b will be found in cell E8. Enter this first guess in cell E17. The coefficient a will be calculated automatically.

  5. Using your first guess for the equation, find the predicted values for the record times in column F.

  6. In column G find the error between the predicted values and the actual data.

  7. In column H find the absolute value of the error.

  8. In column J find the square of the error.

  9. In cell J34 find the mean of the square error.

  10. Adjust the value for b in cell E17 to find the value that gives the minimum mean square error.

  11. Find the equation of the best-fitting curve.

Some EXCEL commands that may be useful are:

LN() - LN(A12) or LN(100). Finds the natural log of a cell.

SUM() - SUM(A12:A21). Finds the sum of the values in cells A12 through A21.

AVERAGE() - AVERAGE(A12:A21). Finds the average of cells A12 through A21.

EXP() - EXP(2) or EXP(A12). Finds e2 or eA12 .

ABS() - ABS(-3) or ABS(A12). Finds the absolute value of a cell.

Return to title page