# 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?

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.