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

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

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

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

Well, I didn't think I could make it uglier, but I believe I've succeeded!! <g

This version of the formula will inhibit displaying that first invalid,
probably unwanted entry where no numeric entries appear earlier in the
column. It basically adds another check that says if sum of cell on same row
plus sum of cell we're looking at earlier in the column is same as value of
cell (in column A) of this row, then don't display anything. But this would
also inhibit display of anything in situations where the earlier number in
column A is zero. Everything seems to have its price. Again, for cell B3 in
our examples (yes, its all one long formula) :

=IF(OFFSET(B3,0,-1)+OFFSET(B3,(CBA(ADDRESS(ROW(B3),COLUMN(A2)))),-1)=OFFSET(B3,0,-1),"",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),""))




"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

Take a look at your original post!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
ps.com...
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

"Take a look at your original post!"
Duh - maybe it's the fact that it's 3 a.m. right now, but I'm not picking up
on the hint?
While the simple formula I originally posted would handle a specific case, I
thought the desire was to just have a "general" formula that would be left on
the sheet down thru the column and then empty out the numbers in column A and
put them back in each month. That's why I scratched my head real hard and
came up with that somewhat nasty general formula?

"RagDyer" wrote:

Take a look at your original post!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
ps.com...
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

You'll notice that my message was addressed to, and sent as an answer to,
the OP!
I wanted him to check on *his* original post on this subject.

http://tinyurl.com/jtjg8

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JLatham" wrote in message
...
"Take a look at your original post!"
Duh - maybe it's the fact that it's 3 a.m. right now, but I'm not picking
up
on the hint?
While the simple formula I originally posted would handle a specific case,
I
thought the desire was to just have a "general" formula that would be left
on
the sheet down thru the column and then empty out the numbers in column A
and
put them back in each month. That's why I scratched my head real hard and
came up with that somewhat nasty general formula?

"RagDyer" wrote:

Take a look at your original post!
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Tom Watt" wrote in message
ps.com...
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

OK! I told you, it was late/early. My bad. Thanks for clarifying my lack
of observation this morning.

"Ragdyer" wrote:

You'll notice that my message was addressed to, and sent as an answer to,
the OP!
I wanted him to check on *his* original post on this subject.

http://tinyurl.com/jtjg8

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JLatham" wrote in message
...
"Take a look at your original post!"
Duh - maybe it's the fact that it's 3 a.m. right now, but I'm not picking
up
on the hint?
While the simple formula I originally posted would handle a specific case,
I
thought the desire was to just have a "general" formula that would be left
on
the sheet down thru the column and then empty out the numbers in column A
and
put them back in each month. That's why I scratched my head real hard and
came up with that somewhat nasty general formula?

"RagDyer" wrote:

Take a look at your original post!
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Tom Watt" wrote in message
ps.com...
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

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