Bob Umlas Interpolation

Learn Excel from MrExcel, Episode 2100 – An Amazing Interpolation Trick from Bob Umlas

Hey, welcome back to the MrExcel netcast. I’m Bill Jelens. 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 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. All right. Well that was the really boring slow tedious way to use the LINEST array formula and then another function to generate those next six years. And while that works, you don't have to do any that. Just select the original numbers and then grab the fill handle and drag. And when you do that, it does all of those crazy steps that I just sped through, all right.

So I call that Straight-Line Regression with Fill Handle. And it's the exact same numbers. It works, all right.

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. Right now I'm going to do the hard way again in fast motion because this podcast isn't about the hard way, all right.

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 got both books and electronic versions of both books while you're waiting for the shipped copies to arrive. Great techniques in those books, all right.

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 a fast motion version of interpolation. Bob's tricks – select 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.

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 from MrExcel.