Leon asks how to forecast the next number in a series of numbers. In Episode 620, I will take a look at a formula solution and a faster solution.
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 NrExcel netcast. I'm Bill Jelen.
I want to send a quick invitation out.
We have a lot of seminars coming up in November if you happen to be near Chicago on the 6th of November, Madison on the 7th, Milwaukee on the 8th, in Springfield, Missouri on the 14th, Virginia Beach on the 15th and then in England on the 19th in Manchester, 21st in Southampton and then back to the United States on the 28th and 29th in Boise Idaho.
If you're near any of those, we'd love to have you come out and attend one of the seminars.
Make sure to stop by and say that you watched the podcast.
Today's question comes from Leon.
Leon says hey, I have a series of numbers maybe five numbers and I need to figure out a formula to forecast the sixth number.
Now there's a couple of different ways to do this.
Today I'm going to approach it with the forecast function and tomorrow we're gonna take a look at a different function that won't require adding in an extra set of numbers.
Here the forecast function basically will give you any position in a series of numbers. So we can ask for the 6th position in this series of numbers but we need to explain to excel that the existing numbers are the 1st through the 5th.
So I'm going to add a new column over here with the numbers 1 through 5.
I'll put in the number 1 and I'll grab the fill handle and hold down the control key to drag that to be 1 through 5.
Okay, so here's the forecast function =FORECAST.
It wants to know which position?
I want the sixth position.
The known y's are the values that I already know and then finally the known x's are just simply that range of 1 through 5.
Finish that function and I get 14.16.
Now what the forecast function is doing? It's doing a straight line regression.
It's figuring out the best fit straight line and then figure out what the sixth position would be.
Now the advantage of having a formula of course is that if any of these numbers change, the forecast will go ahead and update.
But I want to point out that it's possible using the fill handle to basically do the exact same calculation.
here we have the same numbers, I selected all the numbers and grabbed the fill handle and dragged down for one position and it comes up with the exact same answer 14.16.
So when you drag the fill handle in this case, excel really is doing the straight line regression and figuring out the next number.
Now in tomorrow's podcast, we're going to take a look at a way to solve this problem without entering the extra numbers 1 through 5.
Thanks for stopping by. We'll see you tomorrow for another netccast from MrExcel.
I want to send a quick invitation out.
We have a lot of seminars coming up in November if you happen to be near Chicago on the 6th of November, Madison on the 7th, Milwaukee on the 8th, in Springfield, Missouri on the 14th, Virginia Beach on the 15th and then in England on the 19th in Manchester, 21st in Southampton and then back to the United States on the 28th and 29th in Boise Idaho.
If you're near any of those, we'd love to have you come out and attend one of the seminars.
Make sure to stop by and say that you watched the podcast.
Today's question comes from Leon.
Leon says hey, I have a series of numbers maybe five numbers and I need to figure out a formula to forecast the sixth number.
Now there's a couple of different ways to do this.
Today I'm going to approach it with the forecast function and tomorrow we're gonna take a look at a different function that won't require adding in an extra set of numbers.
Here the forecast function basically will give you any position in a series of numbers. So we can ask for the 6th position in this series of numbers but we need to explain to excel that the existing numbers are the 1st through the 5th.
So I'm going to add a new column over here with the numbers 1 through 5.
I'll put in the number 1 and I'll grab the fill handle and hold down the control key to drag that to be 1 through 5.
Okay, so here's the forecast function =FORECAST.
It wants to know which position?
I want the sixth position.
The known y's are the values that I already know and then finally the known x's are just simply that range of 1 through 5.
Finish that function and I get 14.16.
Now what the forecast function is doing? It's doing a straight line regression.
It's figuring out the best fit straight line and then figure out what the sixth position would be.
Now the advantage of having a formula of course is that if any of these numbers change, the forecast will go ahead and update.
But I want to point out that it's possible using the fill handle to basically do the exact same calculation.
here we have the same numbers, I selected all the numbers and grabbed the fill handle and dragged down for one position and it comes up with the exact same answer 14.16.
So when you drag the fill handle in this case, excel really is doing the straight line regression and figuring out the next number.
Now in tomorrow's podcast, we're going to take a look at a way to solve this problem without entering the extra numbers 1 through 5.
Thanks for stopping by. We'll see you tomorrow for another netccast from MrExcel.
