Thanks unfortunately a UDF isnt an option because this has to work in
Pocket Excel too. I never would have posted if so because I would have
just done it in VBA already. :)

In the other thread (linked in this one) I mentioned I got the
suggested formulas to work. Does your long formula do the same thing
as those?

Thanks,

Tom

JLatham wrote:
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 he
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
Just copy the code above and paste it into a code module in the workbook.

"Tom Watt" wrote:

The problem is specifying the cells each month. One month values may
exist in C5 and C12, the next month they may be in C4 and C13. I would
like to create one set of formulas, lock them and they work..

Thanks,

Tom

JLatham wrote:
You should be able to specify a range with the COUNTBLANK function.

in D12, try this formula:

=(C12-C5)/COUNTBLANK(C5:C12)

"Tom Watt" wrote:

Hello,

I have used the LOOKUP formula to find the value of the the next cell
up with data (because there are blank lines seperating them), and this
works. But now I need to divide the difference of two values by the
number of blank lines that seperates them plus 1 (the number of days
that the two values are apart). COUNTBLANK will not work because it
will return the amount of blank cells total in a column, not just ones
that are seperating two values (which are not always in the same
cells).

Example:

This month there happens to be a value 101,010 in C5 and later a value
102,020 in C12. In D12 I would like the difference (102,020-101,010)
divided by the number of cells apart (C12-C5=7).

Thanks,

Tom