Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a regression line from Excel data points
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a regression line from Excel data points
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a regression line from Excel data points
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 "Dominik Petri" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a regression line from Excel data points
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 "Beavoid" wrote: 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 "Dominik Petri" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a regression line from Excel data points
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 "Eric G" wrote: 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 "Beavoid" wrote: 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 "Dominik Petri" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a regression line from Excel data points
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 "Beavoid" wrote: 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 "Eric G" wrote: 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 "Beavoid" wrote: 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 "Dominik Petri" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data points in line graph in Chart function in Excel. | Excel Worksheet Functions | |||
Regression - trend line vs. data points?? | Charts and Charting in Excel | |||
How can I create a line chart with 800,000 data points | Excel Discussion (Misc queries) | |||
How to draw a regression line on 7000+ dated data with Excel 2007 | Charts and Charting in Excel | |||
Line charts with missing data points in Excel 2007 | Charts and Charting in Excel |