I couldn't find/figure out a worksheet function that does the coding
equivalent of
..End(xlUp).Row
perhaps someone will provide one. If I had that, then I could have done
this without a UDF (User Defined Function). But I created a UDF (macro
function) and by using it, I have created this monstrosity that I think will
do what you want with a couple of "watch out for's".
First, the User Defined Function code:
Function CBA(CellAddress As String)
'CBA = Counts Blanks Above
'cell referenced as the CellAddress
Dim EndRow As Long
Dim CurrentAddress As String
CurrentAddress = CellAddress ' black box it
EndRow = Range(CurrentAddress).End(xlUp).Row
CBA = EndRow - Range(CurrentAddress).Row
End Function
Now for the really fun part - the formula to place into the cell where you
want the calculations to be performed.
Assuming a layout where Row 1 on the sheet contains column headers/titles
like:
A B ...other column labels
1 Quantity Average ....other column titles/headers
2 7
3
4
5 8
then in column B, Row 3 (1 row down from 1st real data entry in column A)
enter this formula:
=IF(OFFSET(B3,0,-1)>0,(OFFSET(B3,0,-1)+
OFFSET(B3,(CBA(ADDRESS(ROW(B3),COLUMN(A2)))),-1))/IF((ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1)>0,(ABS(CBA(ADDRESS(ROW(B3),COLUMN(A2))))-1),1),"")
Brief description:
CBA() returns a negative number that can be used as an offset to pick up the
value in a cell above the row you are currently examining.
The IF statement says that if the cell on the same row in column A is > 0,
then go ahead and do the math, otherwise just display an empty string.
The math grabs the value from column A in the same row and adds it to the
value in Column A that is first one above it ahead of intervening blanks, and
then divide that by the absolute value returned by CBA effectively
decremented by 1 ( -4 + 1 = -3, ABS(-3) = 3) but if that number turns out to
be zero (no intervening blank rows) then use 1 as the divisor rather than
zero.
You can then fill this formula down the page as far as you care to and it
will do the math the way I believe you want it to do without ever having to
re-enter formulas. Just delete/add entries in column A and the values in B
are recalculated.
The big thing to watch out for is the fact that the first value in B will be
one you're not interested in, and will probably actually be invalid.
Example, your first numeric entry in A is at row 4, then it is going to try
to take the value in A4 and add it to some value that doesn't exist somewhere
above it, which probably evaluates to zero and place a 4 into B4 and divide
that by the number of blank cells above row 4, probably 3 of them. Wrong
answer!
After that things should work well. Problems could be caused by typing
strange things into column A where the numbers are expected to be.
It's rather ugly, someone may be able to improve upon it, but with the one
major caveat, I believe it's very close to exactly what you are after.
If you need assistance placing the UDF into a code module, instructions for
using the VB Editor to do so can be found here:
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
Just copy the code above and paste it into a code module in the workbook.
