Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Trend line Formulas
Excel has created a formula. And i was wondering if anyone knew how to access the steps as to how excel arrived at that formula. I have done it before, by accident and now I have no idea how.
So I would like some detailed instructions as to how I would be able to see how excel arrived at this equation... The equation/ formula is for a power trend line. Please reply ASAP!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend line Formulas
"ChrissyBubbles101" wrote:
Subject: Trend line Formulas Excel has created a formula. And i was wondering if anyone knew how to access the steps as to how excel arrived at that formula. I have done it before, by accident and now I have no idea how. [....] The equation/ formula is for a power trend line. I am quite sure you did not "arrive at the formula ... by accident". Or you are misusing terminology and asking the wrong question. First, the power trendline formula has the form y = c*x^b, where c and b are derived constants. Is that the formula you refer to? Second, the trendline formula (b and c) is derived from the simple linear regression model for the x and y data. This is not something you stumble upon "by accident". See http://en.wikipedia.org/wiki/Simple_regression. In Excel, b can be derived by the following array-entered formula (press ctrl+shift+Enter instead of just Enter): =COVAR(LN(xData),LN(yData))/VARP(LN(xData)) where xData and yData are ranges (or named ranges) that contain the x and y data. And c can be derived by the following array-entered formula (press ctrl+shift+Enter instead of just Enter): =EXP(AVERAGE(LN(yData))-bFactor*AVERAGE(LN(xData))) where bFactor is a reference to the cell containing the formula above for b. More simply, you can derive b and c by selecting two horizontal cells (e.g. B1 and C1) and array-entering the following formula (press ctrl+shift+Enter instead of just Enter): =LINEST(LN(yData),LN(xData)) The result is b in B1 and ln(c) in C1. Note that C1 is ln(c), not c. So c is EXP(C1). You can determine points along the trendline (i.e. a best-fit curve, which might or more likely might not include any of the actual y data) by entering the following formula: =EXP($C$1)*X2^$B$1 where X2 is a reference to the cell containing some x value. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trend Line | Charts and Charting in Excel | |||
How do I set a Trend Line and Remove the Data Line | Charts and Charting in Excel | |||
Which trend line? | New Users to Excel | |||
trend line does not appear | Charts and Charting in Excel | |||
trend line | Charts and Charting in Excel |