Difference between revisions of "Manuals/calci/LINEST"
Jump to navigation
Jump to search
| Line 29: | Line 29: | ||
<math>Y intercept (b) = INDEX(LINEST(Y, X),2) </math> | <math>Y intercept (b) = INDEX(LINEST(Y, X),2) </math> | ||
| + | *The additional regression is displayed in the following format where each statistic value is described as below- | ||
| + | |||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | | <math>m_n</math> || <math>m_{n-1}</math> || --- || <math>m_1</math> || <math>b</math> | ||
| + | |- | ||
| + | | <math>se_n</math> || <math>se_{n-1}</math> || --- || <math>se_1</math> || <math>se_b</math> | ||
| + | |- | ||
| + | | <math>r_2</math> || <math>se_y</math> || || || | ||
| + | |- | ||
| + | | <math>F</math> || <math>d_f</math> || || || | ||
| + | |- | ||
| + | | <math>ss_{reg}</math> || <math>ss_{resld}</math> || || || | ||
| + | |} | ||
| + | |||
| + | *<math>m_n</math> is an array of constant multipliers for straight line equation | ||
| + | *<math>b</math> is the constant value of Y when X=0 | ||
| + | *<math>se_1</math> is the standard error value for m1 | ||
| + | *<math>se_b</math> is the standard error value for constant b | ||
| + | *<math>r_2</math> is the coefficient of determination | ||
| + | *<math>se_y</math> is the standard error value for Y estimate | ||
| + | *<math>F</math> is the observed F value | ||
| + | *<math>d_f</math> is the number of degrees of freedom | ||
| + | *<math>ss_{reg}</math> is the regression sum of squares | ||
| + | *<math>ss_{resld}</math> is the residual sum of squares | ||
== Examples == | == Examples == | ||
Revision as of 16:06, 30 January 2014
LINEST(Y, X, C , stats)
where,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Y} is a set of Y values,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle X} is an optional set of X values,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle C} is a logical value TRUE or FALSE, that decides whether to force the constant 'b' to 0,
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle stats} is a logical value TRUE or FALSE, that decides whether to return additional regression statistics.
LINEST() is an array function that calculates the statistics for a line by using the 'least squares' method to calculate a straight line that closely fits the input data.
Description
- If 'Y' is the point on y-axis, 'X' is the point on x-axis, 'm' is a constant indicating slope of the line and 'b' is the constant value at which the line crosses y-axis (Y intercept),
then equation of line is -
Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Y=mX + b}
- For multiple ranges of X-values,
Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Y = m1X1 + m2X2 +......+ b}
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle C} is a logical value that decides whether to make constant 'b' equal to 0.
- If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle C} = TRUE or omitted, 'b' is calculated normally. If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle C} = FALSE, 'b' is made equal to 0.
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle stats} is a logical value that decides whether to display additional regression statistics.
- If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle stats} = TRUE, calci returns additional regresstion statistics. If Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle stats} = FALSE or omitted, Calci returns the values of 'm'(slope) and the constant 'b'.
- When there is only one independent X variable, Slope(m) and Y intercept (b) can be calculated using following formulas -
Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Slope (m) = INDEX(LINEST(Y, X),1) }
Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Y intercept (b) = INDEX(LINEST(Y, X),2) }
- The additional regression is displayed in the following format where each statistic value is described as below-
| Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle m_n} | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle m_{n-1}} | --- | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle m_1} | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle b} |
| Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_n} | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_{n-1}} | --- | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_1} | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_b} |
| Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle r_2} | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_y} | |||
| Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle F} | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle d_f} | |||
| Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ss_{reg}} | Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ss_{resld}} |
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle m_n} is an array of constant multipliers for straight line equation
- is the constant value of Y when X=0
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_1} is the standard error value for m1
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_b} is the standard error value for constant b
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle r_2} is the coefficient of determination
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle se_y} is the standard error value for Y estimate
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle F} is the observed F value
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle d_f} is the number of degrees of freedom
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ss_{reg}} is the regression sum of squares
- Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle ss_{resld}} is the residual sum of squares
is the constant value of Y when X=0Examples
| Y co-ordinates | X co-ordinates | |
| 1 | 2 | |
| 5 | 4 | |
| 4 | 6 | |
| 3 | 8 | |
| 2 | 10 |
=LINEST(A2:A6,B2:B6,TRUE,FALSE) : Calculates the statistics of line with Y co-ordinates in cells A2 to A6 and X co-ordinates in cells B2 to B6.
Returns m=0 and b=3 as a result. =LINEST(A2:A6,B2:B6,FALSE,FALSE): Calculates the statistics of line with Y co-ordinates in cells A2 to A6 and X co-ordinates in cells range B2 to B6.
Returns m=0.4090909090909091 and b=0 as a result. =LINEST(A2:A6,B2:B6,FALSE,TRUE) : Displays the additional regression statistics of line with Y co-ordinates in cells A2 to A6 and
X co-ordinates in cells B2 to B6 as shown below -
| 0.40909090909090906 | 0 |
| 0.14373989364401724 | |
| 0.6694214876033057 | 2.1320071635561044 |
| 8.099999999999998 | 4 |
| 36.81818181818181 | 18.181818181818183 |