Join Dennis Taylor for an in-depth discussion in this video Using the TREND and GROWTH functions for projecting future results, part of Excel Tips Weekly.
- This worksheet is depicting sales starting in November 2012 until April 2015. We see the numbers generally going up, and it's reflected in the chart to the right. A few dips here and there. In analyzing data like this, sometimes it's valuable to create a trend line. And you can do this easily simply by right clicking the line and add a trendline. The dialog box that pops up on the right automatically chooses linear. Now you have to know a little bit about regression analysis to make sense of this, but another commonly used option is to choose exponential.
If you're familiar with statistics, you'll understand how these work and the math behind them. And these are the two most frequently used. If I choose exponential, we'll get a different line there. It will be curved. Now also what we can do with these, we can extrapolate into the future based on these. Now another missing ingredient in all this is whether this data is considered very accurate or appropriate. Those familiar with regression know about the term the r squared value on the chart. If we display this and I will with this box here, we don't for the moment see that, but off to the left there we can see it, and it's .9031 which isn't fantastic but it's considered pretty fair.
Again, I'll leave this up to the statisticians. But that's a sensible measure as to whether this particular line is a viable one for prediction purposes. Exponential, almost the same number, but in both cases here, we could also extend this forward. Maybe I'll extend this forward six periods here. And we see what's happening. We have to change the scaling of the chart to see that one. If I turn it back into linear, the line goes here. Now sometimes it's necessary not just to see the line, but instead, or in addition to, have the actual data appear.
In other words, have real numbers to go with this. And there are two functions we can be using here. The trend function and growth function, array functions A-R-R-A-Y. So let's get rid of the lines. I'll do this right in the chart here by selecting the line, getting rid of it, and closing the dialog box to the right. I'm going to use the trend function in column C, reflecting the values that we actually have for sales, but what we're about to now do is create a new line on the chart reflecting this data in column C.
The chart's already set up to handle data in columns C and D. So we highlight the cells in column C ahead of time and type =trend( and we refer to all cells from B2 downward into B31, but because this is an array formula, we don't simply press enter, we press control shift enter. And there's the line that we saw that earlier as a trendline but now we have the numbers to go with it.
And similarly with growth, we'll do the same kind of thing. Highlight these cells ahead of time. And type =growth( and highlight the sales numbers and press control shift enter. And the green line represents the exponential line. Now we can extend these by way of the actual line that we saw earlier, extend these into the future, but because these are trend and growth and they represent linear and exponential, we also have techniques, once we turn these into pure values, of simply extending this downward.
So I'm going to highlight all this data here. And how can we quickly turn these into values? A great Excel shortcut, I'm going to use the right mouse button and temporarily drag these down and right back on top of themselves. Down up, let go of the right mouse button, Copy Here as Values Only. So what could we do with these trend numbers here? Now they're just pure numbers. With them highlighted, I'm going to drag this with the right mouse button, the lower right hand corner, the fill handle, downward six more cells, and choose linear trend.
And we'll have some new numbers on the chart. Now the chart will have to extend into the future, and ideally what we'll do here is take our last two months, highlight those, drag them downward. Click on the chart. And make sure that our chart now extends to include the new data. And we'll also be doing this with growth. So we see where the linear projection will take us. With the growth projection, it'll probably change our upper limit above 30,000,000.
So highlight the data here. This is for growth. And earlier we used the right mouse button, we'll use it again, drag downward, but this time, from the pop up menu, we'll choose growth trend and we see those numbers. And it does go above 30,000,000. The chart might have an absolute value here, simply double click this if it's already set. We'll change that maximum to be, say, 40,000,000. That simply allows the chart to reflect the added data. And there we see it as we close this.
So different techniques. Now again those who understand and use regression analysis are much more familiar with some of the ideas expressed here, but these functions called trend and growth, remember they're array functions, allowed us to actually create the values that reflect the lines that we saw, the linear and the exponential lines. There are not functions available for the other kinds of lines that you saw available, but nevertheless, for these two, we have these and for those who are working with data like this, particularly if that r squared number is a good one, we can make projections based on sound statistical reasoning here using trend and growth functions.
Skill Level Appropriate for all
Excel Tips - New This Week
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.