interpolating

[Guest]

I too have a question about interpolating. I've fit an exponential function to a data set, and plan to use the fitted equation in calculating a value. As I'm reusing this spreadsheet for many projects and am linking it to a Word document, I would like to ask Excel to calculate a value in a cell from the fitted equation. Any ideas?

 

[Guest]

To clarify a bit, I specifically would like to interpolate an x value from a given y.

Thank you.

 

[Dan E]

H,

If you've fit an exponential function to your data set their is no need for interpolation.
Use your fit curve ie.
y = a*e^bx

Interpolation is useful when you have a table
y x
1 2
5 8

so given a y value between 1 and 5 you can use interpolation to estimate the x
value (between 2 and 8)

If you've done your curve fit and obtained
y = a*e^bx
rearrange
y/a = e^bx

ln(y/a) = bx

x = 1/b * ln(y/a)

Dan E

I too have a question about interpolating. I've fit an exponential function to a data set, and plan to use the fitted equation in

calculating a value. As I'm reusing this spreadsheet for many projects and am linking it to a Word document, I would like to ask
Excel to calculate a value in a cell from the fitted equation. Any ideas?

 

[Guest]

Thanks -
What I'm really looking for is a way to dynamically calculate an x value for a given y in a cell from the fitted function, which would change as I update the spreadsheet.

 

[Dan E]

H,

Are you updating the points used to generate the curve or just the data
calculated from the already finished fit?

If the latter then rearrange your fit to make a formula like suggested.

If the former:
Function still of the form
y = a*e^bx

Given Data of x and y add a column =LN(y) (Example dat in A1:A11)
ie rearrangement to form ln(y) = bx + ln(a)

y x ln y
296.8 1 5.693147181
44052.9 2 10.69314718
6538034.7 3 15.69314718
970330390.8 4 20.69314718
1.4401E+11 5 25.69314718
2.13729E+13 6 30.69314718
3.17203E+15 7 35.69314718
4.70771E+17 8 40.69314718
6.98685E+19 9 45.69314718
1.03694E+22 10 50.69314718

In D2:E2 array enter
=LINEST(C2:C11,B2:B11)

this will give you the coefficients b in D2 and ln(a) in E2
in F2 put =LN(E2) to get a

To calculate new y values based on x
=F2*EXP(D2*x)
To calculate new x values based on y
=1/D2*LN(y/F2)

Dan E

Thanks -
What I'm really looking for is a way to dynamically calculate an x value for a given y in a cell from the fitted function, which

would change as I update the spreadsheet.

 

[Guest]

Hello
I was updating the points used to generate the curve

Your instructions worked like a charm - the only change I had to make was
"in F2 put = exp(e2)

Thanks very much for taking the time to write helpful information

----- Dan E wrote: ----

H

Are you updating the points used to generate the curve or just the dat
calculated from the already finished fit

If the latter then rearrange your fit to make a formula like suggested

If the former
Function still of the for
y = a*e^b

Given Data of x and y add a column =LN(y) (Example dat in A1:A11
ie rearrangement to form ln(y) = bx + ln(a

y x ln
296.8 1 5.69314718
44052.9 2 10.6931471
6538034.7 3 15.6931471
970330390.8 4 20.6931471
1.4401E+11 5 25.6931471
2.13729E+13 6 30.6931471
3.17203E+15 7 35.6931471
4.70771E+17 8 40.6931471
6.98685E+19 9 45.6931471
1.03694E+22 10 50.6931471

In D2:E2 array ente
=LINEST(C2:C11,B2:B11

this will give you the coefficients b in D2 and ln(a) in E
in F2 put =LN(E2) to get

To calculate new y values based on
=F2*EXP(D2*x
To calculate new x values based on
=1/D2*LN(y/F2

Dan

Thanks
What I'm really looking for is a way to dynamically calculate an x value for a given y in a cell from the fitted function, whic

would change as I update the spreadsheet