+ Reply to Thread
Results 1 to 3 of 3

meter reading formulas

  1. #1
    Forum Contributor
    Join Date
    06-13-2008
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    277

    meter reading formulas

    Hi Guys,

    Here is my predicament.

    I am trying to make a spreadsheet to simple say how many units have ticked over on a meter between two readings.

    Its normally quite simple

    (A1) Read 1 - 5000

    (A2) Read 2 - 8000

    (A3) Units used - =A2-A1 (3000 Units)

    But then it gets more complicated,

    If the meter has clocked over from 9999 to 0001 between the reads the formula does not handle it.

    So i have made the formula accomodate this using:

    =IF(A2-A1<0,(10000+A2)-A1,A2-A1)

    This all worked fine until i was posed with a problem,

    This spreadsheet needs to be used on a number of different meters:

    4 Rate: 0 - 9999
    5 Rate: 0 - 99999
    6 Rate: 0 - 999999

    I need the formula to accomodate all of these meters within this formula,

    so it needs to first check that the meter has not rolled over, then identify what kind of meter it is if it has rolled over, then add the appropriate amount to add to give the correct number of units.

    (Usually the A1 meter read will have the maximum number of digits if the meter has rolled over) eg the meter will rollover from:

    4759 - 0034 for 4 rate
    73849 - 08576 for 5 rate
    968758 - 004857 for 6 rate

    it would not go from 09845 - 00193 for 5 rate (so the formula should not have a problem identifying the meter type using =IF(AND(A1>10000,A1<99999).......

    Thanks for the help guys!

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    Try
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    06-13-2008
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    277
    thanks martin, worked a treat

+ Reply to 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