Formula for finding next cell with data?

[Tom Watt]

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

 

[Ragdyer]

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

 

[Tom Watt]

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]

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(F1>0,F1+1,1),1)
And copy down to F32.

 

[Tom Watt]

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]

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

 

[Tom Watt]

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

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

 

[RagDyer]

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!

 

[Tom Watt]

Thank you very much for all your help!

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!

 

[RagDyeR]

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