rollover number help

[Nathanael]

I'm working on a spreadsheet to record meter readings from some oil loading
pumps and I've got everything sort ed expect for the following;

The meter numbers we record are 8 digits and when they get to 99999999 they
reset to 00000000, I need a formula which will work out the difference
between the two as if they were carrying on as normal. I.E;
Meter Total Difference
99028990 490500
99519490 490500
00009990 490500

The second thing is that every time I enter a figure starting with 0, Excel
automatically removes the 0 from the beginning which is something I don't
want.

Many thanks for you help

Nathanael

 

[Mike H]

Hi,

Firstly apply a custom format of 000000000 to your cells (That's 9 zeroes)

Then with your meter readings in a1 down but this in B2 and drag down an
additiona cell eaxh time you enter a reading in column A

=IF(A2>A1,A2-A1,(999999999-A1)+A2)

Mike

 

[Nathanael]

Many thanks

I had to adapt it a bit but that's just what I was looking for.
I ended up with the formula looking like this;
=IF(C21=0,,(IF(C21>C20,C21-C20,(999999999-C20)+C21-899999999)))

Thanks again
You've saved me many hours of frustration and the PC from going out the
window.

Nathanael