Daniel, a Tucson subscriber of Go Ask Debbie, asks, “How can I setup a formula that changes when I add more data?”
Ah, formulas that change with you – something that is very easy in Excel, but many people are not taught this simple solution.
Usually when creating a formula in Excel, users type in the Cell Reference. (i.e. A1 or D6)
Let’s say you have a column of temperatures and you are calculating the average. When you setup the formula, you had June’s data; but now you have July’s data also. Since the formula was setup as =Average(B1:B30), this will only calculate the first 30 rows (or June’s data).
To add July’s data, you would have to move the formula and change it to reference the next 31 rows (July’s data). See the picture above for a reference.
Instead of setting up the formula to look at specific rows of data, you can reference only the column.
If the temperatures you wish to average are in Column B, here’s the formula:
Now, no matter how many rows you add to Column B, all of the data will be included.
The same formula will work when calculating Rows of data. (i.e. =Average(3:3)
It’s that simple!