Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    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.

  2. #2
    3 Star Lounger
    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.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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:

    =(B3-B2)/(A3-A2)

    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.

  4. #4
    3 Star Lounger
    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

    =(A3-A2)+(C3-C2)

    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:

    =(B3-B2)/((A3-A2)+(C3-C2))

  6. #6
    3 Star Lounger
    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.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    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(B10-B9)/(A10-A9)/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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    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?

  10. #10
    3 Star Lounger
    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(F2-E2)"
    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!
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    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!".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •