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,
A2:A50into the Name Box (the thing to the left of the Formula Bar).
As you might guess, this will select cells
=A1*1.1, and end with Ctrl+Enter.
This will fill the selected cells with that formula.
But since you used relative addressing (
A3will actually contain
A4will actually contain
Your result will now be in cell
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.