Fitting Line to Exponential Data

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

I should be able to do this but can't figure it out in Excel.

I want to fit a line to some nonlinear data so I can project intermediate
points. I gather I need the LOGEST function for this but I'm not able to
make it work.

Here are the X-Y pairs:

X Y
4.5 0
6 18
10 49
20 77
45 99



Any help, with explanations, would be greatly appreciated so I can do this
on my own next time.

TIA
 
John Smith -
I should be able to do this but can't figure it out in Excel. I want to
Click to expand...
fit a line to some nonlinear data so I can project intermediate points. I
gather I need the LOGEST function for this but I'm not able to make it
work.Here are the X-Y pairs:
X Y
4.5 0
6 18
10 49
20 77
45 99

Any help, with explanations, would be greatly appreciated so I can do this
Click to expand...
on my own next time. <

Before using worksheet functions, I suggest plotting the data using an XY
(Scatter) chart and using Excel's Add Trendline feature to try various curve
fits.

Referring to Chapter 15, Simple Nonlinear Regression, in the book "Data
Analysis Using Microsoft Excel: Updated for Office XP," by Michael R
Middleton:

For the shape of your data, the Power and Log functions are appropriate;
Excel's Exponential (implemented in the LOGEST function) is not.

Also, if the first data point must have Y = 0, then you cannot use Excel's
Power function. You could either adjust that value to Y = 0.01, or add a
constant to each Y value before using the Power function.

After you decide on the appropriate function, you should use more
significant digits of the Add Trendline function to find intermediate
values, or you can use appropriate worksheet formulas to get Excel's usual
15-digit precision.

I could provide detailed steps from the book for the specific functional
form you choose.

- Mike Middleton, www.usfca.edu/~middleton
 
Hi John
I did a fit to C- A*exp(-Bx) using Solver and found a fair fit with
A=158.8428, B= 0.1124 and C=98.24659
Contact me privately and I'll send sample file
 
Michael R Middleton said:
John Smith -

fit a line to some nonlinear data so I can project intermediate points. I
gather I need the LOGEST function for this but I'm not able to make it
work.Here are the X-Y pairs: this
on my own next time. <

Before using worksheet functions, I suggest plotting the data using an XY
(Scatter) chart and using Excel's Add Trendline feature to try various curve
fits.

Referring to Chapter 15, Simple Nonlinear Regression, in the book "Data
Analysis Using Microsoft Excel: Updated for Office XP," by Michael R
Middleton:

For the shape of your data, the Power and Log functions are appropriate;
Excel's Exponential (implemented in the LOGEST function) is not.

Also, if the first data point must have Y = 0, then you cannot use Excel's
Power function. You could either adjust that value to Y = 0.01, or add a
constant to each Y value before using the Power function.

After you decide on the appropriate function, you should use more
significant digits of the Add Trendline function to find intermediate
values, or you can use appropriate worksheet formulas to get Excel's usual
15-digit precision.

I could provide detailed steps from the book for the specific functional
form you choose.

- Mike Middleton, www.usfca.edu/~middleton
Click to expand...

Excellent, thank you. I shall proceed accordingly. I finally got the LOGEST
function to work and can see that it is the wrong equation for a line for
this data.
 
Back
Top