Sometimes the growth in a model is not linear, but it is exponential. In those cases, Excel has forecasting tools to replace LINEST and FORECAST. Episode 623 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Ever Since Leon's Question last Week, I've been talking about how to do forecasting.
Everything we've talked about so far is assuming a straight line situation, where we just have a series that extending in some sort of a straight line.
Here we have some numbers 2, 4, 12, 29, 68, 160, 285.
Those are kind of spiking up rather dramatically and if we used all of the tools we've talked about so far, For example, linest or forecast, we might generate a straight line forecast that just really is not going to do the job.
I want to take a look down here at the graph.
The blue line shows our growth is growing geometrically.
Really I mean it's it's some sort of a organic growth.
Maybe it's the number of bacteria in a petri dish or something like that, and the straight line really is just not going to get it.
I mean here the prediction from the straight line says the next number's going to be 253, which is already less than the previous number.
Clearly not going to work.
Well, sometimes you have something that's not a linear growth but it's a geometric growth.
Excel has a whole bunch of tools for dealing with that instead of "LINEST".
They have "LOGEST".
Basically does the same thing. The Idea here is that the line is growing by multiplication, so we start with some number maybe 2 and then every iteration, it multiplies by some other slope number.
So the Formula is y=b*m raised to the x power.
Where x is the Position,1, 2, 3, 4, 5 and so here, I set up a simple prediction using the "LOGEST" instead of using "LINEST". And let's take a look at the graph.
I'm going to actually make the graph start to point to these numbers instead. So I'll drag the blue box over and we'll generate the new graph.
Now here you see we have our blue line.
Now the pink prediction line is almost perfect.
So there are times if your growth is not linear.
If it's actually geometric that you want to switch over, instead of using "LINEST" to use the "LOGEST" function.
There's also a growth function, where I use the forecast function before the growth function, basically does the same thing for this type of a series.
So just be aware that there's some series, that a linear forecast is gonna be horrible.
You might want to switch over and use a geometric forecast instead.
Hey thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Ever Since Leon's Question last Week, I've been talking about how to do forecasting.
Everything we've talked about so far is assuming a straight line situation, where we just have a series that extending in some sort of a straight line.
Here we have some numbers 2, 4, 12, 29, 68, 160, 285.
Those are kind of spiking up rather dramatically and if we used all of the tools we've talked about so far, For example, linest or forecast, we might generate a straight line forecast that just really is not going to do the job.
I want to take a look down here at the graph.
The blue line shows our growth is growing geometrically.
Really I mean it's it's some sort of a organic growth.
Maybe it's the number of bacteria in a petri dish or something like that, and the straight line really is just not going to get it.
I mean here the prediction from the straight line says the next number's going to be 253, which is already less than the previous number.
Clearly not going to work.
Well, sometimes you have something that's not a linear growth but it's a geometric growth.
Excel has a whole bunch of tools for dealing with that instead of "LINEST".
They have "LOGEST".
Basically does the same thing. The Idea here is that the line is growing by multiplication, so we start with some number maybe 2 and then every iteration, it multiplies by some other slope number.
So the Formula is y=b*m raised to the x power.
Where x is the Position,1, 2, 3, 4, 5 and so here, I set up a simple prediction using the "LOGEST" instead of using "LINEST". And let's take a look at the graph.
I'm going to actually make the graph start to point to these numbers instead. So I'll drag the blue box over and we'll generate the new graph.
Now here you see we have our blue line.
Now the pink prediction line is almost perfect.
So there are times if your growth is not linear.
If it's actually geometric that you want to switch over, instead of using "LINEST" to use the "LOGEST" function.
There's also a growth function, where I use the forecast function before the growth function, basically does the same thing for this type of a series.
So just be aware that there's some series, that a linear forecast is gonna be horrible.
You might want to switch over and use a geometric forecast instead.
Hey thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
