How to create a regression line from Excel data points

[Beavoid]

I was wondering if any of you knew how to create an exponential regression
from a data set in the form of C1e^(C2x). This is the same fashion in which
Excel plots an exponential trend line, but I can't find the right function to
do this. Is there a way to use the same function that Excel does to make its
graph trend lines in Visual Basic. Here is an example of what I need:

X=[1,2,3,4,5]
y=[4,6,10,18,34]

I need a function that would take those variables and, from them, set a
C1e^C2x curve, and spit the C1 and C2 variable back in order to be able to
pull other points off of that regression line.

Let me know if more clarification is necessary and if you have any ideas. I
have been trying to use Growth and LegEst functions in Excel in conjunction
with Visual Basic, but they don't SEEM to be exactly what I am looking for,
but I can find any documentation on other way to do this.

Thanks again everyone,

B

 

[Dominik Petri]

Have a look at Excel's LOGEST() function. It returns a matrix. Make sure
to have a look at the help for this function for clarification.

Hope this helps,
Dominik.

 

[Beavoid]

I have been looking at the LOGEST() function; it says it is an exponential
curve, but doesn't seem to return an exponential, but a logrythmic one. I
need something in the form of C1*e^(C2*X)

Am I wrong? Is it actually returning the constants C1, C2 somehow?

Thanks for the response,

B

Have a look at Excel's LOGEST() function. It returns a matrix. Make sure
to have a look at the help for this function for clarification.

Hope this helps,
Dominik.

I was wondering if any of you knew how to create an exponential regression
from a data set in the form of C1e^(C2x). This is the same fashion in which
Excel plots an exponential trend line, but I can't find the right function to
do this. Is there a way to use the same function that Excel does to make its
graph trend lines in Visual Basic. Here is an example of what I need:

X=[1,2,3,4,5]
y=[4,6,10,18,34]

I need a function that would take those variables and, from them, set a
C1e^C2x curve, and spit the C1 and C2 variable back in order to be able to
pull other points off of that regression line.

Let me know if more clarification is necessary and if you have any ideas. I
have been trying to use Growth and LegEst functions in Excel in conjunction
with Visual Basic, but they don't SEEM to be exactly what I am looking for,
but I can find any documentation on other way to do this.

Thanks again everyone,

B

 

[Eric G]

How about taking the natural logarithm of both sides of the equation y =
C1*e^C2x to get a new linear equation. Then use LINEST on that to get your
coefficients. Then revert back to the logarithmic form.

Just a thought...

Eric

I have been looking at the LOGEST() function; it says it is an exponential
curve, but doesn't seem to return an exponential, but a logrythmic one. I
need something in the form of C1*e^(C2*X)

Am I wrong? Is it actually returning the constants C1, C2 somehow?

Thanks for the response,

B

Have a look at Excel's LOGEST() function. It returns a matrix. Make sure
to have a look at the help for this function for clarification.

Hope this helps,
Dominik.

I was wondering if any of you knew how to create an exponential regression
from a data set in the form of C1e^(C2x). This is the same fashion in which
Excel plots an exponential trend line, but I can't find the right function to
do this. Is there a way to use the same function that Excel does to make its
graph trend lines in Visual Basic. Here is an example of what I need:

X=[1,2,3,4,5]
y=[4,6,10,18,34]

I need a function that would take those variables and, from them, set a
C1e^C2x curve, and spit the C1 and C2 variable back in order to be able to
pull other points off of that regression line.

Let me know if more clarification is necessary and if you have any ideas. I
have been trying to use Growth and LegEst functions in Excel in conjunction
with Visual Basic, but they don't SEEM to be exactly what I am looking for,
but I can find any documentation on other way to do this.

Thanks again everyone,

B

 

[Beavoid]

LogEST gives the exponential parameters; I need a natural exponential form of
the equation. I am not sure that taking the ln woould work. I'll look into
it though.

Thanks

B

How about taking the natural logarithm of both sides of the equation y =
C1*e^C2x to get a new linear equation. Then use LINEST on that to get your
coefficients. Then revert back to the logarithmic form.

Just a thought...

Eric

I have been looking at the LOGEST() function; it says it is an exponential
curve, but doesn't seem to return an exponential, but a logrythmic one. I
need something in the form of C1*e^(C2*X)

Am I wrong? Is it actually returning the constants C1, C2 somehow?

Thanks for the response,

B

Have a look at Excel's LOGEST() function. It returns a matrix. Make sure
to have a look at the help for this function for clarification.

Hope this helps,
Dominik.



Beavoid schrieb:
I was wondering if any of you knew how to create an exponential regression
from a data set in the form of C1e^(C2x). This is the same fashion in which
Excel plots an exponential trend line, but I can't find the right function to
do this. Is there a way to use the same function that Excel does to make its
graph trend lines in Visual Basic. Here is an example of what I need:

X=[1,2,3,4,5]
y=[4,6,10,18,34]

I need a function that would take those variables and, from them, set a
C1e^C2x curve, and spit the C1 and C2 variable back in order to be able to
pull other points off of that regression line.

Let me know if more clarification is necessary and if you have any ideas. I
have been trying to use Growth and LegEst functions in Excel in conjunction
with Visual Basic, but they don't SEEM to be exactly what I am looking for,
but I can find any documentation on other way to do this.

Thanks again everyone,

B

 

[Beavoid]

I found a great method! Very elegant and simple

C1=EXP(INDEX(LINE(LN(y),x),1,2))
C2=INDEX(LINEST(LN(y),x),1)

That's it! Then, just name the cells that those are in and call them from
VBA. Just thought I would let everyone know. Thanks for your time everyone.

B

LogEST gives the exponential parameters; I need a natural exponential form of
the equation. I am not sure that taking the ln woould work. I'll look into
it though.

Thanks

B

How about taking the natural logarithm of both sides of the equation y =
C1*e^C2x to get a new linear equation. Then use LINEST on that to get your
coefficients. Then revert back to the logarithmic form.

Just a thought...

Eric

I have been looking at the LOGEST() function; it says it is an exponential
curve, but doesn't seem to return an exponential, but a logrythmic one. I
need something in the form of C1*e^(C2*X)

Am I wrong? Is it actually returning the constants C1, C2 somehow?

Thanks for the response,

B

:

Have a look at Excel's LOGEST() function. It returns a matrix. Make sure
to have a look at the help for this function for clarification.

Hope this helps,
Dominik.



Beavoid schrieb:
I was wondering if any of you knew how to create an exponential regression
from a data set in the form of C1e^(C2x). This is the same fashion in which
Excel plots an exponential trend line, but I can't find the right function to
do this. Is there a way to use the same function that Excel does to make its
graph trend lines in Visual Basic. Here is an example of what I need:

X=[1,2,3,4,5]
y=[4,6,10,18,34]

I need a function that would take those variables and, from them, set a
C1e^C2x curve, and spit the C1 and C2 variable back in order to be able to
pull other points off of that regression line.

Let me know if more clarification is necessary and if you have any ideas. I
have been trying to use Growth and LegEst functions in Excel in conjunction
with Visual Basic, but they don't SEEM to be exactly what I am looking for,
but I can find any documentation on other way to do this.

Thanks again everyone,

B