Howie asks why the fill handle does what it does when you select four numbers and drag. Episode #1187 discusses trend lines, LINEST, and straight line regression.
...This blog is the video podcast companion to the book, Learn Excel 97-2007.
...This blog is the video podcast companion to the book, Learn Excel 97-2007.
Transcript of the video:
MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Howie.
Howie said that this has been driving him crazy for a while, he finally just went out to Google, and Googled “Excel Expert” and found me, never even seen the podcast before.
So anyway, Howie says he has a series of numbers here, start at a 100, go down at some pace, and he says “When I grab the fill handle and drag, eventually I always get to 0, but what is this?
What is this set of numbers that comes out here?” You know, we have a series, he says “It gets to 0 at a different rate every time, it's not predictable.
What's going on?” OK well, what Excel is doing there is doing a straight line regression, and there's another place where you might have seen this before.
I'm going to have those same 4 numbers here, and I'm going to create a Line chart, just create a little Line chart there.
And I'm going to right-click the line and say Add Trendline, and let Excel add a linear trendline in there, will click Close, and you'll see that Excel does a least-squares best fit.
So, if you paid attention in math, you've heard of that before, it figures out the line that best fits those points, and then extrapolates that line out further.
And both dragging the fill handle and this are a much faster way than actually doing this straight line regression.
To do a straight line regression, you have to choose two cells that are next to each other.
=LINEST of the values that you know, press Ctrl+Shift+Enter, and what you get, you get a y-intercept, and a slope.
So y=mx+b, this is the b, this is the m, and you can set up a little formula here that says “Hey, we're always going to take the 105, and then subtract the point times the slope.” And you see that down here I get those exact same numbers all the way down.
So, when you grab the fill handle and drag very quickly, Excel is doing something, and what it's actually doing is using straight line regression to figure out those values and fill them in.
For you to actually go through into it takes you a good, you know, half a minute to set up the formula, especially if you forget to hit Ctrl+Shift+Enter when you enter LINEST.
So it is pretty amazing, you know, I don't know if that makes it any better, Howie, but kind of does explain what's going on.
So, thanks for finding MrExcel, and stop back tomorrow to get another podcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Howie.
Howie said that this has been driving him crazy for a while, he finally just went out to Google, and Googled “Excel Expert” and found me, never even seen the podcast before.
So anyway, Howie says he has a series of numbers here, start at a 100, go down at some pace, and he says “When I grab the fill handle and drag, eventually I always get to 0, but what is this?
What is this set of numbers that comes out here?” You know, we have a series, he says “It gets to 0 at a different rate every time, it's not predictable.
What's going on?” OK well, what Excel is doing there is doing a straight line regression, and there's another place where you might have seen this before.
I'm going to have those same 4 numbers here, and I'm going to create a Line chart, just create a little Line chart there.
And I'm going to right-click the line and say Add Trendline, and let Excel add a linear trendline in there, will click Close, and you'll see that Excel does a least-squares best fit.
So, if you paid attention in math, you've heard of that before, it figures out the line that best fits those points, and then extrapolates that line out further.
And both dragging the fill handle and this are a much faster way than actually doing this straight line regression.
To do a straight line regression, you have to choose two cells that are next to each other.
=LINEST of the values that you know, press Ctrl+Shift+Enter, and what you get, you get a y-intercept, and a slope.
So y=mx+b, this is the b, this is the m, and you can set up a little formula here that says “Hey, we're always going to take the 105, and then subtract the point times the slope.” And you see that down here I get those exact same numbers all the way down.
So, when you grab the fill handle and drag very quickly, Excel is doing something, and what it's actually doing is using straight line regression to figure out those values and fill them in.
For you to actually go through into it takes you a good, you know, half a minute to set up the formula, especially if you forget to hit Ctrl+Shift+Enter when you enter LINEST.
So it is pretty amazing, you know, I don't know if that makes it any better, Howie, but kind of does explain what's going on.
So, thanks for finding MrExcel, and stop back tomorrow to get another podcast from MrExcel!
