Meter readings negative value?!

[Jordz1991]

I use a spreadsheet at my workplace to record meter readings. However. One of the meters has just reset to zero after surpassing its max of 9999999. So this now shows of course in the overal total as a minus figure. E.g -9877607.

Is there a way to tell the spreadsheet that when it goes back to 00000000 this is not negative and that it's actually just started again?

 

[FDibbins]

maybe this...

	A​	B​
1​	100​
2​	200​	100​
3​	900​	700​
4​	100​	100​
5​	300​	200​

B2=IF(A2 < A1,A2,A2-A1)
copied down

(I know there is a simpler way, cant think of it right now)

 

[thisoldman]

	A	B
1	100
2	200	100
3	900	700
4	100	200
5	300	200

<tbody>
</tbody>

@FDibbins, I believe cell B4 in your example should return 200, not 100 as you show.

Either of these formulas will work in B2 and copied down:

=IF(A2 < A1,1000,0)+A2-A1
or
=1000*(A2 < A1)+A2-A1

 

[pgc01]

Hi

Try in B2:

=MOD(A2-A1,1000)

Copy down

 

[FDibbins]

@FDibbins, I believe cell B4 in your example should return 200, not 100 as you show.

Not sure how you get 200?

Is there a way to tell the spreadsheet that when it goes back to 00000000 this is not negative and that it's actually just started again?

 

[J.Ty.]

I use a spreadsheet at my workplace to record meter readings. However. One of the meters has just reset to zero after surpassing its max of 9999999. So this now shows of course in the overal total as a minus figure. E.g -9877607.

Is there a way to tell the spreadsheet that when it goes back to 00000000 this is not negative and that it's actually just started again?

You ask for a formula which will automatically return result 0 for the reading "00000000" (start of a new meter) and a result 100000000 for the same argument "00000000" (just after completing the whole turn). Unless you use iterative calculations, a single formula cannot give two different results for two identical inputs.

So there is no method for that using plain spreadsheet formulas.

You must either use iterative computations or manually enter an info for the spreadsheet about the number of whole turns of the meter made so far, and update it manually after each such event.

J.Ty.

 

[thisoldman]

Not sure how you get 200?

I inferred that the value wanted was the quantity used in the interval between meter readings. Passing through zero is common with mechanical counters and totalizing flow meters, and you have to correct for it when you require an accurate "overall total."