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!

Try

Please Login or Register  to view this content.

thanks martin, worked a treat