A cool trick from Bob Umlas for our 2000th podcast episde
Fast Motion version of Straight Line Regression
All of those steps can be replicated with the fill handle
Fast motion version of interpolation
Select first, last, and blank cells. Go to Home, Fill, Series
The Fill Series dialog will give you the correct steps
Special Price on both Bob Umlas books: Excel Outside the Box Techniques
Fast Motion version of Straight Line Regression
All of those steps can be replicated with the fill handle
Fast motion version of interpolation
Select first, last, and blank cells. Go to Home, Fill, Series
The Fill Series dialog will give you the correct steps
Special Price on both Bob Umlas books: Excel Outside the Box Techniques
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2100: An Amazing Interpolation Trick from Bob Umlas Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This is my Episode 2100, and if you've been around since the very beginning, and I'm not even sure that I have, I started out with Episode 101 that means this is the 2000th episode.
And what I love is even after 2000 episodes I'm still learning amazing tricks.
I'm going to show you Bob's trick in a minute but it's so fast I'm going to throw in an extra trick here.
Sometimes in Excel, you know, there's a long way to do things and then an amazingly short way.
Now, let's say that I needed to create a Straight Line Regression from these past Sales numbers to generate the next six years.
The point of this episode is not this hard trick, so I'm actually going to speed things up while I do it the hard way.
[ Types of Screen ] Alright, well that was the really boring, slow, tedious way to use the Linest T-array formula and then another function to generate those next six years.
And while that works, you don't have to do any.
Just select the original numbers and then grab the Fill Handle and drag and we do that, it does all of those crazy steps that I just sped through, alright?
So I call that Straight Line Regression with the Fill Handle, and it's the exact same numbers.
It works.
Alright, so here's Bob’s trick: we have the original number and we have the final number we need to fill everything in in the middle.
Alright now, I'm going to do the hard way again in fast motion because this podcast isn't about the hard way.
Alright, so here's the amazing trick that I just learned from Bob.
Put the first number, the last number, all empty cells in between.
Select that whole range from the first to the last.
These have to be numbers not formulas.
FILL, SERIES, and bam!
Right there, it fills in the each step, the increment just click OK and you'll get the exact same numbers that I got using the hard method.
I love it, so I love that there's new things to learn all the time in Excel.
Thanks to Bob Umlas for this trick.
In honor of my 2000th episode, instead of pitching my book this time, I'm going to pitch Bob's two books, Excel Outside the Box, More Outside the Box, 75 unbelievable techniques between these two books and I'm going to price it.
Use that “i” on the top right-hand corner, for just 20 bucks, 2000 pennies.
I get both books and electronic versions of both books while you're waiting for the shipped copies to arrive.
Great, great techniques in those books.
Alright, so we have a cool trick from Bob Umlas for our 2000th podcast episode.
I showed you fast motion version of Straight Line Regression and all those can be replicated with a fill handle, that's one of my tricks.
And then a fast motion version of Interpolation, Bob's tricks like the first, last and blank cells, just Go to Home, Fill, Series and it will be filled in, just click OK.
And you have the correct step amount.
Well, hey, I want to thank you for watching this episode.
I want to thank all the people who watched all the 2000 episodes along the way.
We'll see you next time for another netcast for MrExcel.
This is my Episode 2100, and if you've been around since the very beginning, and I'm not even sure that I have, I started out with Episode 101 that means this is the 2000th episode.
And what I love is even after 2000 episodes I'm still learning amazing tricks.
I'm going to show you Bob's trick in a minute but it's so fast I'm going to throw in an extra trick here.
Sometimes in Excel, you know, there's a long way to do things and then an amazingly short way.
Now, let's say that I needed to create a Straight Line Regression from these past Sales numbers to generate the next six years.
The point of this episode is not this hard trick, so I'm actually going to speed things up while I do it the hard way.
[ Types of Screen ] Alright, well that was the really boring, slow, tedious way to use the Linest T-array formula and then another function to generate those next six years.
And while that works, you don't have to do any.
Just select the original numbers and then grab the Fill Handle and drag and we do that, it does all of those crazy steps that I just sped through, alright?
So I call that Straight Line Regression with the Fill Handle, and it's the exact same numbers.
It works.
Alright, so here's Bob’s trick: we have the original number and we have the final number we need to fill everything in in the middle.
Alright now, I'm going to do the hard way again in fast motion because this podcast isn't about the hard way.
Alright, so here's the amazing trick that I just learned from Bob.
Put the first number, the last number, all empty cells in between.
Select that whole range from the first to the last.
These have to be numbers not formulas.
FILL, SERIES, and bam!
Right there, it fills in the each step, the increment just click OK and you'll get the exact same numbers that I got using the hard method.
I love it, so I love that there's new things to learn all the time in Excel.
Thanks to Bob Umlas for this trick.
In honor of my 2000th episode, instead of pitching my book this time, I'm going to pitch Bob's two books, Excel Outside the Box, More Outside the Box, 75 unbelievable techniques between these two books and I'm going to price it.
Use that “i” on the top right-hand corner, for just 20 bucks, 2000 pennies.
I get both books and electronic versions of both books while you're waiting for the shipped copies to arrive.
Great, great techniques in those books.
Alright, so we have a cool trick from Bob Umlas for our 2000th podcast episode.
I showed you fast motion version of Straight Line Regression and all those can be replicated with a fill handle, that's one of my tricks.
And then a fast motion version of Interpolation, Bob's tricks like the first, last and blank cells, just Go to Home, Fill, Series and it will be filled in, just click OK.
And you have the correct step amount.
Well, hey, I want to thank you for watching this episode.
I want to thank all the people who watched all the 2000 episodes along the way.
We'll see you next time for another netcast for MrExcel.
