MrExcel's Learn Excel # Episode 621 - Array Forecast

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Mar 26, 2009.
Today, we take a second look at Leons question how to forecast the next number in a sequence of numbers. But, in Episode 621, I show you how to solve this with one cell instead of 6 cells.

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!
maxresdefault.jpg


Transcript of the video:
Hey welcome back to the Mr.Excel Netcast.
I'm Bill Jelen.
Now in yesterday's netcast, we had a question from Leon and Leon wanted to know how to extend a series.
Basically we had these set of numbers.
He wanted to be able to have a formula that would forecast the next number in the series.
And I showed how to use the forecast function, but in order to do that, I had to insert a brand new column with the numbers.
Yesterday was one through five.
Today we would need the numbers one through seven.
I want to talk about this function.
Call it the ROW function =ROW (A1) will return to 1 and of course as I copy that down to the other rows, it gives me 2 3 4 5 6 It's just a great way basically to enter a quick series of numbers 1 through 7.
So what we can do is use that function in order to generate the numbers 1 through 7 so =FORECAST.
We want the 8th number now these are the known y's from A1 down through A7 and the known axis just need to be the numbers from 1 through 7. Someone asked for the row of A1 through A7.
Now because that's going to return several numbers that's going to return seven numbers, we have to enter this as an array formula, that means holding down Ctrl and shift while hitting enter.
And excel will figure out exactly what we want and basically give us the 8th number and a series of numbers.
Now let's do a quick test to see if this is working.
I'm going to take those numbers, copy them, paste them over here and drag the fill handle.
Excel will do the same straight line regression, and we get 21.15. So it is actually working.
Now here's a question for you if you're watching the podcast.
This array formula is an interesting formula There's several functions that basically return array formulas.
One of them for example, is the LINEST function.
The Straight line function and if we go into the "Function Arguments" someone else sent a question.
They said. Hey, every time that I use "Function arguments" and the resulting formula is going to be an array formula, there's no way that'll ever work. So let's try it out.
Here we have our known y's, and actually everything else in the function is completely optional and you see that it has two answers 2.86 and -1.7.
That's the slope and the y-intercept.
But if we hit "OK" here, it's going to give us just the first answer of the position.
Do you know any way that using the "Function Arguments" that we can tell excel that this isn't array, and we want to return all the items from the formula?
I tried several things "control shift enter" won't work with the function arguments.
There doesn't seem to be any good way, to get "Function arguments" to return an array formula.
If you know a way, please drop me a note bill@MrExcel.com and we'll get to that on a future podcast.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top