- #EXCEL 2016 REVIEW OF ERRORS IN STATISTICAL FUNCTIONS MANUAL#
- #EXCEL 2016 REVIEW OF ERRORS IN STATISTICAL FUNCTIONS SERIES#
The above table shows the statistic and the value for our example above using both array entered and Index formulas If you want to know the r2 value (discussed later) it is in the 3 rd row, 1 st column.Įg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), 3, 1) The formula can also be entered as a normal equation also using the Index function to extract the array valuesĮg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), Row ,Column) This must be entered as an array formula of 2 columns by 5 rows Linest can also return a number of statistics when Stats parameter is set to TrueĮg: =LINEST(C47:C51, B47:B51, TRUE, TRUE) Ctrl Shift Enter The use of the Index function negates the requirement to use an Array Entered formula. To return both components you must enter the same formula in adjacent cells in the same rowĪnd the equation must be entered as an array formulaĮg: = LINEST(C47:C51, B47:B51, TRUE, FALSE) Ctrl Shift EnterĪlternatively the values can be retrieved from the Linest array function using the Index function Stats = True Return additional regression statisticsįalse Return the m co-efficient and const b =LINEST(C47:C51,B47:B51,TRUE,FALSE) will return the Slope (m) component of the equation Linest is an array formula which must be entered as an array formula to return all the values that it can return.Įg: = LINEST(Known Y Values, Known X Values,Const, Stats) The Linest function can be used to calculate the Slope and Intercept parameters for a linear function
#EXCEL 2016 REVIEW OF ERRORS IN STATISTICAL FUNCTIONS SERIES#
Which we can plot as a new series on our chart We can now use our revised linear equation to plot a line of best fit
To use the above 2 equations we simply enter 2 equations in cells The Intercept function calculates the point at which a linear regression line will intersect the Y-axis by using existing X-values and Y-values.Įg: = INTERCEPT (Known Y values, Known X values) Use The Slope function returns the slope or gradient of the linear regression line through data points in Known_Y’s and Known_X’s.Įg: =SLOPE(Known Y values, Known X values) Intercept
#EXCEL 2016 REVIEW OF ERRORS IN STATISTICAL FUNCTIONS MANUAL#
In the first Post we looked at using a linear equation in the form Y=mX + c to express our estimated line of best fit which we manual estimated was linear.Įxcel has 2 functions which we can use to calculate the actual slope (m) and intercept (c) for the above equation. Excel Functions and ToolsĮxcel has a number of Worksheet functions specifically designed to assist us with analysing various trends. Logarithmic – Approximating a Logarithmic lineĮxponential – Approximating an Exponential lineĮxcel supports the use of these trend types in a number of ways. Polynomial – Approximating a Polynomial function to a power There are a number of standard types of trends which can be classified as:
However in real life data is rarely this simple.įortunately Excel has a Number of Functions and Tools that allow us to look for trends and use the data natively for forecasting purposes. In the first post we looked at some simple data with only a few points and a trend that was very fairly obvious or was it.Ī number of other linear trends could have equally been used and all look about right. means multiply eg 10.2.M.X = 10.2 * m * x Why do we need to use Excel Functions? ^ means raise to the power eg: 10^2 = Power(10,2) = 100
In this post I will be using the following nomenclature Readers are encouraged to follow along at your own pace and use the examples in the Examples Workbook attached.Īll charts, tables and diagrams in this post with the associated Excel formulas are included in the Example workbook. This post is going to delve slowly at first and then deeper into some of Excels Statistical Functions. In this post we will look at how we can use Excel built in functions to aid us in forecasting. In the previous post we looked at Manual Forecasting techniques and how Excel can be used to assist. Forecasting using Excel Functions “Todays forecast will be Hot and Humid with a Chance of Snow?” (Even the experts with big computers get it wrong)
0 kommentar(er)
