Closed Thread
Results 1 to 9 of 9

Meter reading calculator based on date

  1. #1
    Forum Contributor
    Join Date
    12-31-2008
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    149

    Meter reading calculator based on date

    Hi,

    Please see attached

    As part of a project, I need to come up with a meter reading calculator. I start of with 3 base meter readings (A2, F2, K2), which would be read from the meter. 3 reads are needed in case the consumption changes dramatically at some point. I then need Excel to work out the reading for dates in between. The formula needs to be dynamic enough so that I can change any of the grey dates to what I want.
    The estimates need to be calculated using an average daily consumption. This figure needs to be displayed in D9.
    It would be preferable if macros weren't used. If its possible to explain what to do without an attachment, that would be good too.

    Any help would be great.

    Thanks.
    Attached Files Attached Files
    Last edited by bd528; 07-17-2009 at 06:38 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Meter reading calculator based on date

    Don't know did I understand well... But is this close
    Book1(1).xls

  3. #3
    Forum Contributor
    Join Date
    12-31-2008
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Meter reading calculator based on date

    Thanks for your efforts but this isnt quite right.

    The use should only enter figures in cells A2 F2 K2. All the other cells in row 2 should be automatically populated.

    So in my exmaple, excel needs to work out the average daily usage between 01/01/08 and 12/06/08, and then work out values for the dates in row A.

    Any other ideas?

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Meter reading calculator based on date

    Quote Originally Posted by bd528 View Post
    As part of a project
    What sort of project, is this a homework assignment?

    You can calculate the average usage per day by dividing the amount used in a period by the number of days in that period

    Once you have the average per day then for any date you can multiply that amount by the number of days passed since the start date....and add that to the reading as of the start date

  5. #5
    Forum Contributor
    Join Date
    12-31-2008
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Meter reading calculator based on date

    Its for a work project.

    I can work out the daily average, I just cant do the formula so that excel works out values for the dates I can change on row A.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Meter reading calculator based on date

    You have one cell for average daily consumption but that will differ for dates before and after F1 because the average will be calculated using different start and end points

    You can use this formula in B2 copied across to E2

    =$A2+(B1-$A1)*($F2-$A2)/($F1-$A1)

    You need to change the formula again for the cells G2:J2, see attached
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-31-2008
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Meter reading calculator based on date

    Thank you. Thats exactly what I need.

    Solved

  8. #8
    Registered User
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Meter reading calculator based on date

    How i calculate the daily meter reading. I have only one meter and i want calculte the daliy usage. Pl guide me how i calculate the meter reading in the excel sheet with example...

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Meter reading calculator based on date

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1