Extrapolating distant data in Excel

Posted on

QUESTION :

Often I find myself having to drag out functions across ranges in Excel in order to find out what something will look like at a distant point. For example:

A1 = 100
B1 = 150
C1 = =(A1+B1)
D1 = =(B1+C1)
E1 = =(C1+D1)
etc....

Or another example would be:

A1 = 100
B1 = =(A1*1.1)
C1 = =(B1*1.1)
D1 = =(C1*1.1)
etc....

If I want to know what the 50th iteration will look like, regardless of what this formula actually does, is there a function I can use to calculate the nth iteration?

ANSWER :

This is really more of a question about mathematics than Excel specifically. Your first example looks like the Fibonacci sequence with different starting numbers, and the second is simply adding 10% to a starting figure. So after 500 iterations, your figure will be 1.1^500*100. There isn’t a simple formula that will extrapolate anything – you need to carefully consider what you’re doing mathematically.

Well, this might start to become a little impractical if you want the 500th iteration,
but if you’re only interested in the 50th, why not just create a series of 50 cells in Excel? 
You may find it a bit more practical to use a column rather than a row,
because the rows are numbered. Taking your second example,

  • Type 100 into cell A1.
  • Type A2:A50 into the Name Box (the thing to the left of the Formula Bar). 
    As you might guess, this will select cells A2 through A50.
  • Type =A1*1.1, and end with Ctrl+Enter
    This will fill the selected cells with that formula. 
    But since you used relative addressing (A1 rather than $A$1),
    A3 will actually contain A2*1.1, A4 will actually contain A3*1.1, etc.

Your result will now be in cell A50
If you do decide to go for 500, and you don’t want to do all that scrolling to get to the bottom, just type A500 into the Name Box.

Leave a Reply

Your email address will not be published.