|
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
Formula for finding next cell with data?
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 |
| All times are GMT +1. The time now is 04:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com