MrExcel's Learn Excel #622 - LINEST Regression

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.
Rather than using FORECAST to choose the next number, you can use LINEST to generate the slope and y-intercept of a line. Todays podcast graphs the results of LINEST so you can see how Excel is forecasting the next number in a series. Episode 622 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!
maxresdefault.jpg


Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, you know a week ago.
Leon asked a question about extending a series and he kind of hit a nerve here because I used to do a lot of work in a forecasting department.
And so, I'm just going to kind of go on hear more about forecasting and we have this series over here in A1 through A8 and we talked about how to use the forecast function.
Today, I want to take a look at the line ST function.
The straight line function.
It's an interesting question because it returns an array and to use it we have to select two cells that are next to each other.
So, in this case cell C2 and cell D2 equal LINEST and give it the known 'ys'.
These are the values in the series, that's all we need and I have to hit [ ctrl + shift + enter ] it returns two numbers for us the first number happens to be the slope.
The second number is the y-intercept.
Now, I hate to drag it back through math here, but basically any straight line is defined as 'y' is equal to mx+b.
'b' is the intercept and then 'mx' is the slope.
Basically, what it says is we start at negative 2 and every period we add 2.97.
So, I can create a little prediction formula over here.
The prediction formula says, hey!
We're going to start with this y intercept and I'll hit [ F4 ] to make sure that's absolute plus the slope times the row of A1.
Basically, that will extend this series as I copy it down.
So, the prediction here is that we're going to get numbers like 0.87, 3.84 and when we graph this, basically what we'll see is that the blue line is not necessarily straight.
The blue line or the actual z's, kind of you know start out.
Sloping up a little bit faster and the prediction the straight line function or the forecast function is basically finding the straight line that has the least amount of error.
So, it drew a straight line between all those points and the next point in the series is just an extension of that straight line.
So, if we go back and would use the forecast function or you know, even just drag the fill handle to ask for the next number, we're basically getting the value that lies along that straight line.
That's how the straight line function work.
The LINEST function is again annoying because you have to be able to put it in two cells.
You have to select two cells first, and hit [ ctrl + shift + enter ] and that assumes that we have just a single regression.
Sometimes when you're doing multiple regression, you need to select a larger area or if we're asking for statistics like the R-squared value.
You'll ask for a larger area, that's a topic for another day.
I do want to briefly though, point out that it's possible to figure out just the slope or just the Y-intercept, by using the index.
So, if I wanted to see just the slope, I could ask for equal INDEX, the LINEST function.
These are the known 'ys'.
and then which row do I want?
I want the first row.
Which column do I want?
I want the first column that will give me the slope.
To get the y-intercept, I would take that exact same function, but ask for the second column.
So, you can imagine that instead of using the forecast function, that we used in previous netcast.
I could build a formula here that would ask for basically the slope first and then the y-intercept, do the math and come up with the 26.61131.
So, that's yet another option if you need to figure out the next number in a series.
Hey! Thanks for stopping by, I will see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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