I want to set up a compound interest table. I want to start with $15 and multiply by 1.0125 indefinitely. I want to be able to see the answer for each calculation (for example, I want to see what the answer is after 39 calculation, after 74 calculations, 22 calcualtions, etc..)
David, try this:
In A1 enter 15.00
In A2 enter 1.0125
In B1 (with the rest of Column B empty) enter this formula:
HOW IT WORKS
The ROUND wrap is obviously just truncating results to two decimal places. You could remove it and just format the entire Column B as currency.
ArrayFormula(...) tells Sheets to process a range (real or virtual) of data.
If you hadn't applied the interest yet, the formula would be the following:
That's because anything to the zero power is 1. So this would just return 15.00.
If you wanted the amount after one application of 1.0125, this would be the formula:
And so on.
So the only part that changes is the "power" to which the interest is raised.
We can accomplish an unlimited sequence with SEQUENCE.
What the SEQUENCE call says (in plain English) is this: "Form a virtual array that is 900 rows tall and 1 column wide, starting at 0 (and moving up by 1 each time by default)."
The ArrayFormula wrapping acts on this sequence and provides a result for each step.
Of course, if you wanted to "hard-wire" your numbers into the formula instead of referring to external cells A1 and A2, you'd just pop them in directly:
Just by the way, there is a fourth possible argument to SEQUENCE, which is the step. If you leave it out, the default is "count by 1 each time." But if you wanted only every, say, 5th step, you'd divide 900 by 5 to get 180 rows and you'd add a fourth argument of 5, like this:
... or in context of the full formula at hand ...
15in cell A1.
=A1*1.0125in cell A2.
Select cell A2, hover your mouse cursor over the bottom right of the cell until a crosshair appears.
Drag the crosshair downwards (e.g. to cell A20) and release.
Alternatively, if you need hundreds or thousands of rows:
=A1*1.0125in cell A2.
Select cell A2, and use Ctrlc to copy it.
Select the entire column A by clicking on "A" in the column header, then Ctrlv to paste the formula into all rows of column A.
Change the contents of cell A1 to