MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Bob Umlas Interpolation

June 12, 2017 - by Bill Jelen

Bob Umlas Interpolation

Awesome trick for interpolation in Excel. If you need 10 values between 117 and 235, you can have Excel calculate this without a formula.

Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2100 an amazing interpolation trick from
  • Bob Umlas hey welcome back to the
  • districts I'll NetCast I'm bill Genesis
  • 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
  • 2000 and what I love is even after 2000
  • episodes I'm still learning amazing
  • tricks I'm going to show you Bob's truck
  • in a minute but it's so fast I'm gonna
  • 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 Linus
  • tea 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 the
  • fill handle and it's the exact same
  • numbers it works all right so here's
  • BOMs trick we have the original number
  • and we have the final number we need to
  • fill everything in in the middle all
  • right now I'm going to do the hard way
  • again in fast motion because this
  • podcast isn't about the 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 left 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 on
  • list for this trick in honor of my 2000
  • 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 gonna 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 grit
  • techniques in those books all right so
  • we have a cool trick from Bob wellness
  • for our 2000 podcast episode I showed
  • you fast motion version a 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 blank cells just go
  • to home fill series and it will be
  • filled in just click OK and you have the
  • correct step amount okay 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 net cast
  • for MrExcel

Download File

Download the sample file here: Podcast2100.xlsm

Title Photo: Leks2 / Pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.