Does Excel have a formula that will automatically return the value of
the next cell with data? What I mean is I have a spreadsheet with a
row for each day of the month (1-31) and a few times a week someone
will go in and put data in for that day, leaving blank lines inbetween
the days that data was collected. I would like to be able to, for
example, find the difference between two values in a column, but they
are not neccasarily the same distance apart from each other each month.
So a simple formula (=C5-C2) doesnt work. Instead I need something
that will detect the value that is in the next cell with data up (ex:
C2) and use that value. Does a formula like this exist? If not, I
would do it in VBA but I don't think Pocket Access supports VBA, does
it?

I understand I could just put the data grouped together without blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data into
it and it needs to be in 31 day of month format.

Thanks,

Tom

Don't quite understand which cell you want to use *besides* the last cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty) cells
between C2 and the last data containing cell, this formula will subtract C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the value of
the next cell with data? What I mean is I have a spreadsheet with a
row for each day of the month (1-31) and a few times a week someone
will go in and put data in for that day, leaving blank lines inbetween
the days that data was collected. I would like to be able to, for
example, find the difference between two values in a column, but they
are not neccasarily the same distance apart from each other each month.
So a simple formula (=C5-C2) doesnt work. Instead I need something
that will detect the value that is in the next cell with data up (ex:
C2) and use that value. Does a formula like this exist? If not, I
would do it in VBA but I don't think Pocket Access supports VBA, does
it?

I understand I could just put the data grouped together without blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data into
it and it needs to be in 31 day of month format.

Thanks,

Tom

OK so there's hope! I will study the lookup formula. Basically here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would want
the difference of C5 and C8 divided by the number of days inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would automatically
put these type of results regardless of what data is in column C. Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the last cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty) cells
between C2 and the last data containing cell, this formula will subtract C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the value of
the next cell with data? What I mean is I have a spreadsheet with a
row for each day of the month (1-31) and a few times a week someone
will go in and put data in for that day, leaving blank lines inbetween
the days that data was collected. I would like to be able to, for
example, find the difference between two values in a column, but they
are not neccasarily the same distance apart from each other each month.
So a simple formula (=C5-C2) doesnt work. Instead I need something
that will detect the value that is in the next cell with data up (ex:
C2) and use that value. Does a formula like this exist? If not, I
would do it in VBA but I don't think Pocket Access supports VBA, does
it?

I understand I could just put the data grouped together without blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data into
it and it needs to be in 31 day of month format.

Thanks,

Tom

I took 3 columns to do this, so you might decide to hide some if you wish.

Headers
C1 = Meter Read
D1 = Usage
E1 = Avg.Btwn.Reads
And, I'm using Column F as a counter.

Assume:
C2 to C32 for daily readings, with random days being empty.
C2 (beginning of month) will *always* have a reading (number).
D2 & E2 = Empty

Enter this formula in D3:
=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))
And copy down to D32.

Enter this formula in E3:
=IF(D3="","",D3/F2)
And copy down to E32.

Enter this formula in F*2*
=IF(D2="",IF(F10,F1+1,1),1)
And copy down to F32.

--
HTH,

RD

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

"Tom Watt" wrote in message
oups.com...
OK so there's hope! I will study the lookup formula. Basically here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would want
the difference of C5 and C8 divided by the number of days inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would automatically
put these type of results regardless of what data is in column C. Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the last cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty) cells
between C2 and the last data containing cell, this formula will subtract
C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the value of
the next cell with data? What I mean is I have a spreadsheet with a
row for each day of the month (1-31) and a few times a week someone
will go in and put data in for that day, leaving blank lines inbetween
the days that data was collected. I would like to be able to, for
example, find the difference between two values in a column, but they
are not neccasarily the same distance apart from each other each month.
So a simple formula (=C5-C2) doesnt work. Instead I need something
that will detect the value that is in the next cell with data up (ex:
C2) and use that value. Does a formula like this exist? If not, I
would do it in VBA but I don't think Pocket Access supports VBA, does
it?

I understand I could just put the data grouped together without blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data into
it and it needs to be in 31 day of month format.

Thanks,

Tom

Sorry for the delayed reply. I did get this working, and appreciate
your help. I wonder though can it be taken one step further and be
able to handle meter roll-overs?

Thanks!

Tom

RagDyer wrote:
I took 3 columns to do this, so you might decide to hide some if you wish.

Headers
C1 = Meter Read
D1 = Usage
E1 = Avg.Btwn.Reads
And, I'm using Column F as a counter.

Assume:
C2 to C32 for daily readings, with random days being empty.
C2 (beginning of month) will *always* have a reading (number).
D2 & E2 = Empty

Enter this formula in D3:
=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))
And copy down to D32.

Enter this formula in E3:
=IF(D3="","",D3/F2)
And copy down to E32.

Enter this formula in F*2*
=IF(D2="",IF(F10,F1+1,1),1)
And copy down to F32.

--
HTH,

RD

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

"Tom Watt" wrote in message
oups.com...
OK so there's hope! I will study the lookup formula. Basically here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would want
the difference of C5 and C8 divided by the number of days inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would automatically
put these type of results regardless of what data is in column C. Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the last cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty) cells
between C2 and the last data containing cell, this formula will subtract
C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the value of
the next cell with data? What I mean is I have a spreadsheet with a
row for each day of the month (1-31) and a few times a week someone
will go in and put data in for that day, leaving blank lines inbetween
the days that data was collected. I would like to be able to, for
example, find the difference between two values in a column, but they
are not neccasarily the same distance apart from each other each month.
So a simple formula (=C5-C2) doesnt work. Instead I need something
that will detect the value that is in the next cell with data up (ex:
C2) and use that value. Does a formula like this exist? If not, I
would do it in VBA but I don't think Pocket Access supports VBA, does
it?

I understand I could just put the data grouped together without blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data into
it and it needs to be in 31 day of month format.

Thanks,

Tom

What's the roll-over (max) number?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
oups.com...
Sorry for the delayed reply. I did get this working, and appreciate
your help. I wonder though can it be taken one step further and be
able to handle meter roll-overs?

Thanks!

Tom

RagDyer wrote:
I took 3 columns to do this, so you might decide to hide some if you
wish.

Headers
C1 = Meter Read
D1 = Usage
E1 = Avg.Btwn.Reads
And, I'm using Column F as a counter.

Assume:
C2 to C32 for daily readings, with random days being empty.
C2 (beginning of month) will *always* have a reading (number).
D2 & E2 = Empty

Enter this formula in D3:
=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))
And copy down to D32.

Enter this formula in E3:
=IF(D3="","",D3/F2)
And copy down to E32.

Enter this formula in F*2*
=IF(D2="",IF(F10,F1+1,1),1)
And copy down to F32.

--
HTH,

RD

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

"Tom Watt" wrote in message
oups.com...
OK so there's hope! I will study the lookup formula. Basically here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would want
the difference of C5 and C8 divided by the number of days inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would automatically
put these type of results regardless of what data is in column C. Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the last
cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty)
cells
between C2 and the last data containing cell, this formula will
subtract
C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the value
of
the next cell with data? What I mean is I have a spreadsheet with a
row for each day of the month (1-31) and a few times a week someone
will go in and put data in for that day, leaving blank lines
inbetween
the days that data was collected. I would like to be able to, for
example, find the difference between two values in a column, but
they
are not neccasarily the same distance apart from each other each
month.
So a simple formula (=C5-C2) doesnt work. Instead I need something
that will detect the value that is in the next cell with data up
(ex:
C2) and use that value. Does a formula like this exist? If not, I
would do it in VBA but I don't think Pocket Access supports VBA,
does
it?

I understand I could just put the data grouped together without
blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data
into
it and it needs to be in 31 day of month format.

Thanks,

Tom

Well it varies, but an example is 99,999,999

RagDyer wrote:
What's the roll-over (max) number?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
oups.com...
Sorry for the delayed reply. I did get this working, and appreciate
your help. I wonder though can it be taken one step further and be
able to handle meter roll-overs?

Thanks!

Tom

RagDyer wrote:
I took 3 columns to do this, so you might decide to hide some if you
wish.

Headers
C1 = Meter Read
D1 = Usage
E1 = Avg.Btwn.Reads
And, I'm using Column F as a counter.

Assume:
C2 to C32 for daily readings, with random days being empty.
C2 (beginning of month) will *always* have a reading (number).
D2 & E2 = Empty

Enter this formula in D3:
=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))
And copy down to D32.

Enter this formula in E3:
=IF(D3="","",D3/F2)
And copy down to E32.

Enter this formula in F*2*
=IF(D2="",IF(F10,F1+1,1),1)
And copy down to F32.

--
HTH,

RD

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

"Tom Watt" wrote in message
oups.com...
OK so there's hope! I will study the lookup formula. Basically here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would want
the difference of C5 and C8 divided by the number of days inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would automatically
put these type of results regardless of what data is in column C. Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the last
cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty)
cells
between C2 and the last data containing cell, this formula will
subtract
C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the value
of
the next cell with data? What I mean is I have a spreadsheet with a
row for each day of the month (1-31) and a few times a week someone
will go in and put data in for that day, leaving blank lines
inbetween
the days that data was collected. I would like to be able to, for
example, find the difference between two values in a column, but
they
are not neccasarily the same distance apart from each other each
month.
So a simple formula (=C5-C2) doesnt work. Instead I need something
that will detect the value that is in the next cell with data up
(ex:
C2) and use that value. Does a formula like this exist? If not, I
would do it in VBA but I don't think Pocket Access supports VBA,
does
it?

I understand I could just put the data grouped together without
blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data
into
it and it needs to be in 31 day of month format.

Thanks,

Tom

You'll only have to change the formula in D3 from:

=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))

TO

=IF(C3="","",MOD(C3-LOOKUP(99^99,C$2:C2),99999999))

And copy down to D32.

Everything else is the same!

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
oups.com...
Well it varies, but an example is 99,999,999

RagDyer wrote:
What's the roll-over (max) number?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
oups.com...
Sorry for the delayed reply. I did get this working, and appreciate
your help. I wonder though can it be taken one step further and be
able to handle meter roll-overs?

Thanks!

Tom

RagDyer wrote:
I took 3 columns to do this, so you might decide to hide some if you
wish.

Headers
C1 = Meter Read
D1 = Usage
E1 = Avg.Btwn.Reads
And, I'm using Column F as a counter.

Assume:
C2 to C32 for daily readings, with random days being empty.
C2 (beginning of month) will *always* have a reading (number).
D2 & E2 = Empty

Enter this formula in D3:
=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))
And copy down to D32.

Enter this formula in E3:
=IF(D3="","",D3/F2)
And copy down to E32.

Enter this formula in F*2*
=IF(D2="",IF(F10,F1+1,1),1)
And copy down to F32.

--
HTH,

RD

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

"Tom Watt" wrote in message
oups.com...
OK so there's hope! I will study the lookup formula. Basically
here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would
want
the difference of C5 and C8 divided by the number of days inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would automatically
put these type of results regardless of what data is in column C.
Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the
last
cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty)
cells
between C2 and the last data containing cell, this formula will
subtract
C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or
formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the
value
of
the next cell with data? What I mean is I have a spreadsheet
with a
row for each day of the month (1-31) and a few times a week
someone
will go in and put data in for that day, leaving blank lines
inbetween
the days that data was collected. I would like to be able to,
for
example, find the difference between two values in a column, but
they
are not neccasarily the same distance apart from each other each
month.
So a simple formula (=C5-C2) doesnt work. Instead I need
something
that will detect the value that is in the next cell with data up
(ex:
C2) and use that value. Does a formula like this exist? If not,
I
would do it in VBA but I don't think Pocket Access supports VBA,
does
it?

I understand I could just put the data grouped together without
blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data
into
it and it needs to be in 31 day of month format.

Thanks,

Tom

Thank you very much for all your help!


RagDyer wrote:
You'll only have to change the formula in D3 from:

=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))

TO

=IF(C3="","",MOD(C3-LOOKUP(99^99,C$2:C2),99999999))

And copy down to D32.

Everything else is the same!

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
oups.com...
Well it varies, but an example is 99,999,999

RagDyer wrote:
What's the roll-over (max) number?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tom Watt" wrote in message
oups.com...
Sorry for the delayed reply. I did get this working, and appreciate
your help. I wonder though can it be taken one step further and be
able to handle meter roll-overs?

Thanks!

Tom

RagDyer wrote:
I took 3 columns to do this, so you might decide to hide some if you
wish.

Headers
C1 = Meter Read
D1 = Usage
E1 = Avg.Btwn.Reads
And, I'm using Column F as a counter.

Assume:
C2 to C32 for daily readings, with random days being empty.
C2 (beginning of month) will *always* have a reading (number).
D2 & E2 = Empty

Enter this formula in D3:
=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))
And copy down to D32.

Enter this formula in E3:
=IF(D3="","",D3/F2)
And copy down to E32.

Enter this formula in F*2*
=IF(D2="",IF(F10,F1+1,1),1)
And copy down to F32.

--
HTH,

RD

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

"Tom Watt" wrote in message
oups.com...
OK so there's hope! I will study the lookup formula. Basically
here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would
want
the difference of C5 and C8 divided by the number of days inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would automatically
put these type of results regardless of what data is in column C.
Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the
last
cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty)
cells
between C2 and the last data containing cell, this formula will
subtract
C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or
formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD

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


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the
value
of
the next cell with data? What I mean is I have a spreadsheet
with a
row for each day of the month (1-31) and a few times a week
someone
will go in and put data in for that day, leaving blank lines
inbetween
the days that data was collected. I would like to be able to,
for
example, find the difference between two values in a column, but
they
are not neccasarily the same distance apart from each other each
month.
So a simple formula (=C5-C2) doesnt work. Instead I need
something
that will detect the value that is in the next cell with data up
(ex:
C2) and use that value. Does a formula like this exist? If not,
I
would do it in VBA but I don't think Pocket Access supports VBA,
does
it?

I understand I could just put the data grouped together without
blank
lines (as we are are presently doing), but I'm trying to make the
sheets compatible with another program so we can copy/paste data
into
it and it needs to be in 31 day of month format.

Thanks,

Tom

You're welcome, and appreciate the feed-back.
--

Regards,

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

"Tom Watt" wrote in message
ups.com...
Thank you very much for all your help!


RagDyer wrote:
You'll only have to change the formula in D3 from:

=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))

TO

=IF(C3="","",MOD(C3-LOOKUP(99^99,C$2:C2),99999999))

And copy down to D32.

Everything else is the same!

--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Tom Watt" wrote in message
oups.com...
Well it varies, but an example is 99,999,999

RagDyer wrote:
What's the roll-over (max) number?
--
Regards,

RD


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

-------------------------------------------------------------------------
--
"Tom Watt" wrote in message
oups.com...
Sorry for the delayed reply. I did get this working, and appreciate
your help. I wonder though can it be taken one step further and be
able to handle meter roll-overs?

Thanks!

Tom

RagDyer wrote:
I took 3 columns to do this, so you might decide to hide some if you
wish.

Headers
C1 = Meter Read
D1 = Usage
E1 = Avg.Btwn.Reads
And, I'm using Column F as a counter.

Assume:
C2 to C32 for daily readings, with random days being empty.
C2 (beginning of month) will *always* have a reading (number).
D2 & E2 = Empty

Enter this formula in D3:
=IF(C3="","",C3-LOOKUP(99^99,C$2:C2))
And copy down to D32.

Enter this formula in E3:
=IF(D3="","",D3/F2)
And copy down to E32.

Enter this formula in F*2*
=IF(D2="",IF(F10,F1+1,1),1)
And copy down to F32.

--
HTH,

RD


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

-------------------------------------------------------------------------
--

"Tom Watt" wrote in message
oups.com...
OK so there's hope! I will study the lookup formula. Basically
here's
a better example maybe this will make sense:

Meter readings exists in cells C5, C8 and C12. In cell D8 I would
want
the difference of C5 and C8 divided by the number of days
inbetween
them. In cell D12 I would want the difference between C8 and C12
divided by the number of days between those. I'd want to put the
formula though in 31 cells in the D column so it would
automatically
put these type of results regardless of what data is in column C.
Does
this make more sense?

Thanks!

Tom

Ragdyer wrote:
Don't quite understand which cell you want to use *besides* the
last
cell
with data in it.

If your month goes from C2 to C32 (31 days),
And your last cell with data was C20, with numerous blank (empty)
cells
between C2 and the last data containing cell, this formula will
subtract
C2
from that last data cell:

=LOOKUP(99^99,C2:C32)-C2

If, on the other hand, there are 0's in those other cells, or
formulas
equating to null (""), try this:

=LOOKUP(2,1/((C2:C32<"")*(C2:C32<0)),C2:C32)-C2

--
HTH,

RD


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

-------------------------------------------------------------------------
--


"Tom Watt" wrote in message
ups.com...
Does Excel have a formula that will automatically return the
value
of
the next cell with data? What I mean is I have a spreadsheet
with a
row for each day of the month (1-31) and a few times a week
someone
will go in and put data in for that day, leaving blank lines
inbetween
the days that data was collected. I would like to be able to,
for
example, find the difference between two values in a column,
but
they
are not neccasarily the same distance apart from each other
each
month.
So a simple formula (=C5-C2) doesnt work. Instead I need
something
that will detect the value that is in the next cell with data
up
(ex:
C2) and use that value. Does a formula like this exist? If
not,
I
would do it in VBA but I don't think Pocket Access supports
VBA,
does
it?

I understand I could just put the data grouped together without
blank
lines (as we are are presently doing), but I'm trying to make
the
sheets compatible with another program so we can copy/paste
data
into
it and it needs to be in 31 day of month format.

Thanks,

Tom