A few weeks back, an IYM reader emailed to ask me an Excel question:How can I have Excel sum up, in a single cell, the last six entries in an always-lengthening column?
I cruised the 'net for bit, searching for an answer ... and found not much.
So I took the task upon myself. And after a wee bit of Excel play, here's the formula I settled on (assuming that Column A contains the dynamic data):
In Excel, the OFFSET function is used to return a reference to a cell or range of cells. Since we're looking to sum up the last six entries, we need to use OFFSET function to determine that six-cell range. Arguments of the OFFSET function look like so:
OFFSET(reference, rows, columns, height, width)
In my "Sum Last Six" spreadsheet, Cell A1 is the reference point from which Excel determines the range.
The next step, then, is to have Excel determine the location of the last numeric entry in Column A. (This will constitute the "row" reference in the OFFSET function.) We accomplish this by use of the MATCH function. To have Excel locate the number of the row containing the last numeric entry in Column A, I use the following:
We then need to subtract 1 from whatever row the MATCH function returns, as our reference (Cell A1) is already located one row down in Column A.
For the "columns" argument, we're already in Column A, which is where the range we want OFFSET to return is located. We enter zero here because Excel doesn't need to move either left or right, column-wise, to get to the range we want.
We want the "height" of the range to be minus-six. Doing this tells Excel that our range includes the six cells previous to the last numeric entry in Column A.
Our range is only one column in "width," so we enter one as this OFFSET argument.
And that's all there is to it! Excel will now SUM (or AVERAGE, or whatever function you choose) the last six entries in Column A, always using the six most-recently entered figures in that column.
