Are You Trendy? (Part 2)

Posted on January 26th, 2011 in Charts and Graphs , Excel Howtos , Huis , Learn Excel , Posts by Hui - 29 comments

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)

In the previous post we looked at Manual Forecasting techniques and how Excel can be used to assist. In this post we will look at how we can use Excel built in functions to aid us in forecasting.

This post is going to delve slowly at first and then deeper into some of Excels Statistical Functions. Readers are encouraged to follow along at your own pace and use the examples in the Examples Workbook attached.

All charts, tables and diagrams in this post with the associated Excel formulas are included in the Example workbook.

In this post I will be using the following nomenclature

^ means raise to the power eg: 10^2 = Power(10,2) = 100

.  means multiply eg 10.2.M.X = 10.2 * m * x

Why do we need to use Excel Functions?

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.

A number of other linear trends could have equally been used and all look about right.

However in real life data is rarely this simple.

Fortunately Excel has a Number of Functions and Tools that allow us to look for trends and use the data natively for forecasting purposes.

There are a number of standard types of trends which can be classified as:

Linear – Approximating a straight line

Polynomial – Approximating a Polynomial function to a power

Power – Approximating a power function

Logarithmic – Approximating a Logarithmic line

Exponential – Approximating an Exponential line

Excel supports the use of these trend types in a number of ways.

Excel Functions and Tools

Excel has a number of Worksheet functions specifically designed to assist us with analysing various trends.

They are categorised by type below

Excel Functions for Linear Trends

  • Slope
  • Intercept
  • Linest
  • Trend
  • Forecast

Excel Functions for Exponential Trends

  • Logest
  • Growth

Other Excel Tools

  • Excel Chart + Trendline


USING EXCELS WORKSHEET FUNCTIONS


Linear Estimates

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.

Excel has 2 functions which we can use to calculate the actual slope (m) and intercept (c) for the above equation.

Slope

The Slope function returns the slope or gradient of the linear regression line through data points in Known_Y’s and Known_X’s.

eg: =SLOPE(Known Y values, Known X values)

Intercept

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.

eg: = INTERCEPT (Known Y values, Known X values)

Use

To use the above 2 equations we simply enter 2 equations in cells

m = SLOPE(C47:C51, B47:B51)                     = 1.298

c = INTERCEPT(C47:C51, B47:B51)            = 0.140

We can now use our revised linear equation to plot a line of best fit

Y = m.X + c

Y = 1.298.X + 0.140

So for

X = 5, Y= 6.63 &

X = 30, Y = 39.07

Which we can plot as a new series on our chart


Linest

The Linest function can be used to calculate the Slope and Intercept parameters for a linear function

Linest is an array formula which must be entered as an array formula to return all the values that it can return.

Eg:   = LINEST(Known Y Values, Known X Values,Const , Stats)

=LINEST(C47:C51,B47:B51,TRUE,FALSE) will return the Slope (m) component of the equation

Const = True b parameter is calculated

False b is set to 0 (zero)

Stats =  True Return additional regression statistics

False Return the m co-efficient and const b

To return both components you must enter the same formula in adjacent cells in the same row

and the equation must be entered as an array formula

Eg: = LINEST(C47:C51, B47:B51, TRUE, FALSE) Ctrl Shift Enter

Slope (m) Intercept (c)
Linest 1.298 0.140

Alternatively the values can be retrieved from the Linest array function using the Index function

Gradient m =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),1)

Intercept c =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),2)

The use of the Index function negates the requirement to use an Array Entered formula.

Stats

Linest can also return a number of statistics when Stats parameter is set to True

Eg: =LINEST(C47:C51, B47:B51, TRUE,TRUE) Ctrl Shift Enter

This must be entered as an array formula of 2 columns by 5 rows

The formula can also be entered as a normal equation also using the Index function to extract the array values

Eg:          = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), Row ,Column)

If you want to know the r2 value (discussed later) it is in the 3rd row, 1st column.

Eg:          = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), 3 , 1)

The above table shows the statistic and the value for our example above using both array entered and Index formulas

The r2 parameter highlighted will be discussed later.

Trend

The Trend function is used to calculate a straight line best fit line based on a number of known X & Y values.

Values of Y can be calculated for values of X inside or outside the know range of X values and so Trend can be used to interpolate or extrapolate data.

eg:          = INTERCEPT (known Y values, known X values, New X Value, Const)

Const    = True; Calculate the Intercept value

= False; Set the Intercept value c = 0

If for example you are using this to model your power cost.

If you have a fixed monthly cost plus a cost per kW, you would set Const to True

If you have no fixed monthly cost and are only charged per kW set Const to false

eg:          =TREND($C$101:$C$105,$B$101:$B$105,B106,TRUE)

Forecast

The Forecast function is used to calculate a straight line best fit line based on a number of known X & Y values.

Values of Y can be calculated for values of X inside or outside the know range of X values and so Trend can be used to interpolate or extrapolate data.

eg:      = FORECAST (New X Value, Known Y values, Known X values)

= FORECAST(B129, $C$124:$C$128, $B$124:$B$128)


Non-Linear Estimates

So far our examination of trends has revolved around the use of linear estimates and the Excel functions that support that.

But as we saw above there are lots of cases where non-linear estimates are required.

This section will deal with the following estimate types.

  • Polynomial – Approximating a Polynomial function, a.x^n + b.x^(n-1) + c.x^(n-2) + … + m = 0
  • Power – Approximating a Power function, y = a.x^b
  • Logarithmic – Approximating a Logarithmic line, y = b.ln(x) + a
  • Exponential – Approximating an Exponential line, y = b.m^x

Luckily Excel has a number of function and some tools to assist us here as well.

Exponential Functions

Exponential functions are based around the formula y = b.m^x

Excel has one function specific to growth estimates and that is the Logest function.

As with Linest, Logest is an array function.

eg:     =LOGEST(Known Y’s, Known X’s, Const, Stats)

=LOGEST(C6:C13, B6:B13, true, false)  Ctrl Shift Enter

Const = True or omitted b parameter is calculated

False b is set to 1

Stats =  True Return additional regression statistics in an array

False Return the m co-efficient and const b

Alternatively the values can be retrieved from the Logest array function using the Index function

B = INDEX( LOGEST( C6:C13, B6:B13, True, False), 1)

X = INDEX( LOGEST(C6:C13, B6:B13, True, False), 2)

The use of the Index function negates the requirement to use an Array Entered formula.1

However Logest, is a tricky function as it actually just passes values to the Linest function!

So we can actually use the Linest function for doing nearly all of our Exponential, Logarithmic and Power function trends.

But you ask “Doesn’t Linest give us the parameters for a straight line?”

Absolutely.

To use Linest to analyse an Exponential function we need to unwrap it so to speak and that is done by taking the Log of the Y values prior to putting them into the Linest equation, like this:

Form:    = LINEST( LN(Known Y Values), Known X Values)

eg:          = LINEST( LN(C32:C39), B32:B39) Which is an array formula

or            = INDEX( LINEST( LN(C32:C39), B32:B39), 1) as a normal formula

Now the tricky part is that the m component or array parameter 2 must now be converted back to an exponential so we can use exp(m component) or  =EXP( INDEX( LINEST( LN(C32:C39), B32:B39),2))

This is difficult to explain but is shown in a worked example on the Exponential Functions section of the Non-linear Functions page of the example workbook attached.

Growth

The Growth function can be used to calculate an exponential curve that best fits your data based on a number of known X & Y values.

Form:    = LINEST(Known Y Values, Known X Values, New X Values)

eg:          = GROWTH($C$32:$C$39, $B$32:$B$39, B40) as a normal formula

This is also shown in a worked example on the Exponential Functions section of the Non-linear Functions page of the example workbook attached.

Logarithmic Functions

Logarithmic functions are based around the formula y = b.LN(x)+a

Excel doesn’t have a specific function dealing with Logarithmic functions, however we can use the Linest function as previously described by first converting the data from a Logarithmic to Straight line and this is done by talking the LN of the X values.

Form:    = LINEST( Known Y Values, LN(Known X Values))

eg:          = LINEST( LN(C32:C39), B32:B39) as an array formula

or            = INDEX( LINEST( LN(C32:C39), B32:B39), 1) as a normal formula

This is shown in a worked example on the Logarithmic Functions section of the Non-linear Functions page of the example workbook attached.

Power Functions

Power functions are based around the formula y = a.x^b

Excel doesn’t have a specific function dealing with Power functions, however we can again use the Linest function as previously described by first converting the data from a Power function to Straight line and this is done by talking the LN of the X and Y values.

Form:    =LINEST( LN(Known Y Values), LN(Known X Values))

eg:          =LINEST( LN(C58:C65), LN(B58:B65)) as an array formula

or            =INDEX( LINEST( LN(C58:C65), LN(B58:B65)), 1) as a normal formula

The above equations return Parameter 1 as b and Parameter 2 as LN(a)

LN(a) must be converted back to Parameter a by taking the Exp(a)

This is shown in a worked example on the Power Functions section of the Non-linear Functions page of the example workbook attached.

Polynomial Functions

Polynomial functions are based around the formula y = a.x^n + b.x^(n-1) + c.x^(n-2) + … + m

Which typically looks like  y = a.x^5 + b.x^4 + c.x^3 + d.x^2 + e.x +m

And if any of the parameters a to m are zero that part of the function will be zero and not shown.

Excel does have a specific function dealing with Polynomial functions, and you guessed it, it is the Linest function. The Linest function must be told that it is dealing with a polynomial function and this is done by adding another parameter to it’s input. The extra parameter is added by raising the know X values to the power of an array of number 1..n, where n is the power of the polynomial you want to use.

Form:  = LINEST( Known Y Values, Known X Values^{1,2,3,..n})

eg:     for a polynomial of power 3

= LINEST(C84:C94, B84:B94^{1,2,3}) as an array formula

or      =INDEX( LINEST(C84:C94, B84:B94^{1,2,3}), 1) as a normal formula

The above equations return Parameter 1 as a, Parameter 2 as b, Parameter 3 as c if a power 3 polynomial is used.

This is shown in a worked example on the Power Functions section of the Non-linear Functions page of the example workbook attached.


Multiple Variable Linear Regressions

The Linest function is able to be used to determine the regressions of multiple input variables (X1, X2, … Xn)  that may contribute to a single output variable (Y).

This is best demonstrated with a simple example:

Hui’s Fruit Shop

Say we have a Fruit Shop and we only sell Apples & Oranges and we know how many Staff and what our Overhead Costs were and how much Profit we have made each year for the past decade.

This could be tabulated below:

We can use Linest to work out a regression for this model. That is what is the relationship between the output and all the inputs.

The format of this will be

Form:    = LINEST(Known Y values, Known X Values, TRUE, TRUE) as an Array Formula

eg:          = LINEST(E122:E132, A122:D132, TRUE, TRUE)

Note that the Known X Values of this example is a 4 column wide area representing the 4 variables.

This must be array entered in an area Xn + 1 columns wide and 5 rows deep, in our case a 5 column x 5 row area.

Note that the equation for then profit is made up of the array values from the first row of the answer array in reverse order

Y = 18.84.X1 + 27.98.X2 + 3851.79.X3 -0.26.X4 -15406.84

And that the parameters are in highest X4 to lowest X1 order followed by b at the end

You can also see the other parameters of the array of which the most important is the r2 factor which in this example is 0.90 indicating that there is a good fit between the Inputs and the Profit. Hence we could be relatively comfortable using our profit equation for the estimate of future profits.

Measuring the accuracy of the Estimation.

In the linear Linest section at the start and in the previous example we briefly mentioned a measure called the r2 parameter and said that because it had a value of 0.90 we would be comfortable using our estimation parameters to estimate future profits.

r2 is a measure of the error between the data points and the estimated values.

Its values vary between 0 = no relationship and 1 = a perfect relationship.

For example here are 3 charts based on the equation of Y = 3 X + 5

The equations of the lines of best fit and the r2 values are shown on each chart.

You can see that the data of Chart Y1 has a very close fit to the equation both visually and through a very high r2 value of 0.9962, where as at Y3 there is a very loose relationship between the data and the estimate which is shown visually as well as a low r2 value of 0.2552.

The derivation and use of this is beyond this post and I would refer you to the Excel Help of the Linest function, where it is discussed or Wikipedia.

How Does All This Work ?

The Excel Linest, Logest and Growth Functions all use a technique called “Least Squares Approximation”.

This is an iterative process which minimises the sum of the square of the distance from the estimated line to the actual data for all known data points. Once this is minimised the parameters which define the estimated line are returned to the user.

The scope of how Least Squares works is beyond the scope of this post, but if you are interested have a read at Wikipedia.

There are a number of other estimation techniques available which excel doesn’t support.

One should never assume that “just because Excel gave me the answer – it is correct” and this applies to the use of statistics more than any other area in maths or Excel usage.

Limitations:

The above techniques need to be used with a degree of caution.

Often a trend will exactly mathematically fit the data but in reality you wouldn’t use the equations.

In the picture below (courtesy of Wikipedia) 10 data points are exactly matched by a Polynomial function , whereas the linear estimate misses every point.

Which estimate would you choose to use?  The linear function I hope.

This is discussed in more detail at Wikipedia.

Disclaimer

It should be noted that just because Excel returns an estimated line of best fit to your data, it doesn’t mean that your data actually follows that trend, it just may be coincidental and that user discretion is advised in all cases, refer Limitations above.

There are a number of other estimation techniques available and users interested should discuss these if required with a person expert in their data distribution.

Excel Functions Referred to in this Post

Exp – Return the exponential value of the input

Forecast – Forecast intermediate or future values based on known X and Y values

Growth – Derive an exponential estimate for a known set of X & Y values

Index – Lookup a value at row/column intercept from a table or array of data

Intercept – Return the intercept of a linear estimate

Linest – Derive a linear estimate for a known set of X & Y values

LN – Return the Natural Log value of the input

Logest – Derive an exponential estimate for a known set of X & Y values

Power – Returns the value of a number raised to a power

Slope – Return the slope of a linear estimate

Trend – Forecast intermediate or future values based on known X and Y values

Further Readings

Excel has a number of extra Statistical functions hidden in the Data Analysis addin.

I have not discussed or used these tools here as not all users will have access to them and the post is getting longish already.

Functions you may want to have a look at include:

Correl & Pearson: Both functions allow the calculation of correlation coefficients between variables.

Exponential Smoothing: The Exponential Smoothing analysis tool predicts a value that is based on the forecast for the prior period, adjusted for the error in that prior forecast

Fourier Analysis: The Fourier Analysis tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data, great for analysing periodic and frequency based data.

I would direct readers who are interested in using these techniques to look at the following sources

Microsoft Excel Help – Statistical Functions

Wikipedia

Physics Labs Tutorials

Newton Excel Bach, not (just) an Excel Blog

 

Further Readings

Are You Trendy (Part 1)

Are You Trendy (Part 3)

 

What’s Next ?

In the next post we will looks at some Tools that Excel has to assist us in quickly determining which estimate method we can use.

I will also give you a neat little UDF to assist in your interpolations/extrapolations of your data which was used to make the animated GIF at the top of the first post.

ps: Happy Australia Day Everyone :) !

Your email address is safe with us. Our policies

Written by Hui...
Tags: , , , , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

29 Responses to “Are You Trendy? (Part 2)”

  1. [...] This post was mentioned on Twitter by Chandoo.org and Stray__Cat, Excel Insider. Excel Insider said: Are You Trendy? (Part 2): Forecasting using Excel Functions “Todays forecast will be Hot and Humid with a Chance… http://bit.ly/dX8kBX [...]

  2. Cyril Z. says:

    Nice second part but I’ve notice some kind of cut/paste between TREND and FORECAST which disturb my understanding of the differences between them.

    Can you explain a bit more how they differ ?

  3. Hui... says:

    @Cyril
    They give the same results but ask for input in a slightly different way.
    Forecast is useful for a single point
    Trend is an array function and can do 1 point as a normal function or a number of points as an array function

  4. bill says:

    again, a wonderful post. quick questions: are you suggesting that r2 be the only evaluative statistic on model form and goodness of fit? what statistic could/should be used to compare “goodness of fit” across all linear and non-linear regression model forms? how can it be derived from the Excel formulas?

  5. John M. says:

    I’ve used about 27 difference variations of the linear model transforming X and/or Y with square root, squared, natural log, and reciprocal. This includes the linear model and two models for when both X and Y have values between 0 and 1. These two models are the logistic and log probit models.

  6. Prem Sivakanthan says:

    Hi Hui, great post…very timely as I was just scratching my head yesterday trying to work out the growth function. I have a question: one of the data models I was working on was corrupted (not by excel, but by its lesser equivilent, a human being) so I need to estimate a set of data points for December 2010. The data in question was definiately not linear, and has no clear trend, upward or downward. I’m thinking some type of average would be the best way to go (I have a couple of years worth of data), what would be your thoughts?

    Prem
    from EHA

  7. C.S. Ganti says:

    Excellent tutorial. I must say that not many are aware of these features in EXCEL — how they added from pure Pivot Tables to all the way in Data Analysis and array functions all types – Stats, Math, Date and many more. Thank you, I am intimately familiar what you presented here and have used at one time or another…

    Thanks, may be MS will give you some credit for advancing their tools beyond their own publicity.

    Keep it coming

  8. Fred says:

    Wonderful Post. Man, I’m going to bookmark it for later reference.

    @bill. r2 is very good only if the outcome has certain randomness (think stock price). But like Hui has shown in the last chart. An polynomial chart hitting almost all the dots like that may have r2 = .99 or so, given the known x and y. but the straight line which miss all the points may have a r2 = .96 or .95 So in that case I “eye-ball” them and make a human judgement as to which to use. Right, Hui?

  9. [...] For more details on the individual Tren Types refer to Are You Trendy (Part 2). [...]

  10. Pablo Molina says:

    Chandoo: Googled 2 days for information related to logarithmic trends, including Microsoft sites and Excel forums.

    None of them had a comprehensible, complete, graphical, professional explanation that match your level.

    Congratulations!

  11. [...] be a part of your forecasting system. Now, let's have an overview of the FORECAST function in Excel.Let's begin by saying that FORECAST function in Microsoft Excel is not a complete inventory forecast…el. WordPress › [...]

  12. [...] A useful post I found about Logest. The handiest tip I found is to use the “Index” function when using LOGEST, which makes it not necessary to enter it as an array formula. [...]

  13. Lewis says:

    Excellent. Thank you…

  14. T Lyle says:

    Thanks so much for this great resource. I do have a question though. In theory, I would think that Slope and Trend are both trying to get to the same thing, right? Aren’t they both based on finding the line of best fit? If so, I would think that the following might be true (although it isn’t):

    Let’s assume a time series (monthly data) of rates and I am trying to predict the next value in that series (think extending a forward curve). I would think that if I took the Slope of the series and added it to the last number in the series, I should get a reasonable ‘forecast’ for the next month. However, this doesn’t always match the number that TREND or FORECAST predict. I think if you have a perfect R^2 then perhaps it matches. Not sure. But in most real situations it doesn’t match.

    Is this because [SLOPE + Last Data Point] doesn’t take into account the volatility or ‘tightness’ of the line of best fit, and TREND / FORECAST do? Very interested in any suggestions.

    • Hui... says:

      @Tracy
      Your totally correct about the r2 value and its impact on adding the slope to the last value for linear functions
      If r2=1.0 then the last value will be on the average slope line and adding the slope will give you the next Y value for the next X Value

      There is a small but important caveat here.

      Dates, especially Monthly data is not linear on the X Axis, because some months have 28, 29, 30 or 31 days
      so you do need to be careful when assuming this applies to Date based data.
      It sort of depends is the data based on say an accumulation of data from the month in which case the no of days is important or is the Month just a name for the X Axis and the data is independent of the days per month.

      • T Lyle says:

        Thank you for your quick response. I understand about the daily/monthly issue. In my case, I am looking at a monthly rate so the amount of days in a month doesn’t matter, but the point is well taken.

        So just to confirm, Trend & Forecast use the variance in the R^2 to inject additional volatility (based on passed behavior) back into the future forecast which moves it off of (but within the overall regression and continuing to maintain the line of best fit) the perfect slope line? If so, I think I am on the same page.

        And a Logarithmic forecast would basically be the inverse of a Growth forecast, right? Based on the shape of the curve?

        Thanks a ton!

  15. manjunathan says:

    dear sir …. how to calculate a,b, and c from Y=ax+by+c using excel 2007.
    i know Y ,x,and y values but i need a,b ,c sd, R values
     

  16. Pandu Rakimanputra says:

    BIG THanks pal..
     
    It’s very useful for me… Keep posting

  17. SATYA SWARUP says:

    I need to use Winter-Holt’s model of forecasting in Excel. If there is any user defined function, it would be of great help to me.

  18. David says:

    Hi Hui, great work, this was very helpful!

    However, the link for downloading the Trend2.xls file seems to be broken. I was able to download the Trend1 and Trend 3 files successfully though. Can you repost it or email it to me?

    Thank you!

  19. Tim says:

    Chandoo – This is awesome; great example workbook too! I never leave your site without knowing a little bit more about excel and what it can do. Thanks a ton.

    Tim

Leave a Reply