Statistical calculations in excel manual#
Statistical calculations in excel series#

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

Statistical calculations in excel 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

Statistical calculations in excel 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)