Results 1 to 11 of 11

20020715, 13:48 #1
 Join Date
 Mar 2001
 Location
 Nixa, Missouri, USA
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
Gas meter readings (XP, 2000, 97)
I have been assigned the task of creating a meter reading spreadsheet. What is wanted are the following columns. Time of reading, meter reading. hourly consumption, avg hourly consumption based on history, weekly total (Sun 11:30pm to Sun 11:29pm), daily totals, daily avg consumption.
The reading will be taken by hand and randomly at best. Each report or spread sheet is to be one month. (workbook of 12 months that can link to the previous year for comparisons).
Basically someone will read the meter say at 7:14 am and it will be 1234. The meter will be read again at say 8:36 am with a reading of 1345. The end users want to know the actual consumption, How much was used from 7:30 am to 8:30 am, how much we have used for the day and how much we used yesterday in comparison.
I have XP, The main reader of the information has 2000, and I am postive the poor soul that has to collect the data will have 97.

20020715, 14:17 #2
 Join Date
 Mar 2001
 Location
 Nixa, Missouri, USA
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Gas meter readings (XP, 2000, 97)
Attached is a example of what We had someone try to create. It seems too complicated.

20020715, 14:30 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Gas meter readings (XP, 2000, 97)
To compute extrapolated hourly or daily consumption:
Let's assume Times are in column A, Meter readings in column B.
Daily consumption extrapolated from data in row 2 and 3:
=(B3B2)/(A3A2)
The reason for this is that Excel stores dates and times internally as numbers in units of 1 day (times are represented as fractions of a day).
To get the extrapolated hourly consumption, divide this by 24.
I hope this helps to get you started.

20020715, 15:18 #4
 Join Date
 Mar 2001
 Location
 Nixa, Missouri, USA
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Gas meter readings (XP, 2000, 97)
This is great for an individual day. How do I roll over from one day to the next.
Colum "C" is the date.

20020715, 15:31 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Gas meter readings (XP, 2000, 97)
You could store date + time in a single column  Excel can do that.
But I'll try to answer for the situation that date and time are in different columns.
Still assuming times are in Column A and meter readings in column B, and now dates in column C:
To compute the time difference, you can use all kinds of IF formulas to see if the date has changed from one row to the next, but a simple way is
=(A3A2)+(C3C2)
If C3 = C2, only the times will be subtracted (A3 must be greater than A2).
If C3 > C2, the difference in days (integer) will be added to the difference in time (fraction); in that case, A3 may be less than A2.
So to compute extrapolated daily gas consumption:
=(B3B2)/((A3A2)+(C3C2))

20020715, 15:56 #6
 Join Date
 Mar 2001
 Location
 Nixa, Missouri, USA
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Gas meter readings (XP, 2000, 97)
I cannot get that to work.
Attached is what I have set up.

20020715, 19:37 #7
 Join Date
 Mar 2001
 Location
 Nixa, Missouri, USA
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Gas meter readings (XP, 2000, 97)
re: (A3 must be greater than A2).
If my last reading on a day is at 9:00 pm (21:00) and my first reading of the next day is 7:00 am (07:00) then A3 cannot be greater than A2.
However you mentioned that date and time could be in the same cell. Maybe I'll try that. Using the formula =SUM(B10B9)/(A10A9)/24 Where "B" is the meter reading and "A" is the Date/Time I have been successful in going from one day to the next. Now on to one month to the next.

20020716, 05:19 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Gas meter readings (XP, 2000, 97)
Don't know if it is still relevant; here is a modified version of your attachment.
You had the correct formulas for daily average in column F. To get hourly averages, just divide the values in column F by 24; no need to set up complicated formulas for that.

20020724, 15:12 #9
 Join Date
 Mar 2001
 Location
 Nixa, Missouri, USA
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Gas meter readings (XP, 2000, 97)
I am still having trouble here. Now I need to get the monthly total. Since I do not know when the last data will be collected or how may times a month it will be collected I have no way of knowing the cell range to get the monthly total from. Is there a formula that will check a column and state "if data is present go to next cell if no data end here/ now subtract last cell with data from first cell with data."?
What I need is a running total of column A. If I do this and it uses all 65K plus cells times twelve worksheets (one per month) won't that create a larger file than needed?

20020724, 17:59 #10
 Join Date
 Mar 2001
 Location
 Nixa, Missouri, USA
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Gas meter readings (XP, 2000, 97)
Ok, here is what I did. <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>
off on E2 I put "=min(B3:B1000)" in F2 I put "=max(b3:B1000)" and then his those cells.
Next in D2 I put "=sum(F2E2)"
This limits the data to 1000 pieces of information per month however if I get data 8 times a day (first shift figuring 2nd and 3rd shift will not cooperate) times 5.5 work days in a week time 4.33 weeks in a month I cam up with 190 pieces of data input per month.
<img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
Now I need to save this as a template so only information can be added to column A and Column B starting with Cell 3. I also need to hide the "#DIV/0" in columns D, E, F and then hide I3 and J3.
Any help would be great!

20020724, 21:52 #11
 Join Date
 Dec 2000
 Location
 La Verne, California, USA
 Posts
 312
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Gas meter readings (XP, 2000, 97)
Kent,
You are trying to subtract a word("Reading") in B2 from a number in B3. Look at the formula in the cell where it says "VALUE!".