Meter Readings

Hi Tracy,

If by Multiplier you mean it says on the meter " x 100 " then simply
multiply, (or divide as appropriate), the result of the formula by 100:

=MOD(D2-C2,10^LEN(D2))*100


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Maria Tracey" wrote in message
...

"Sandy Mann" wrote in message
...
Tracy,

Daddy doesn't seem to be around a the moment. I would suggest making the
meter reading entries text by formatting the cells as text *before*
entering the reading or preceding them with an apostrophe if they are
already entered, (so that the leading zeros will be counted as
characters), then using Daddy's excellent formula, modify it to:

=MOD(D2-C2,10^LEN(D2))


Thanks, I'll give it a try.

What about the multipliers and dividers ?
Any idea how to tackle that ?

"Sandy Mann" wrote in message
...
Hi Tracy,

If by Multiplier you mean it says on the meter " x 100 " then simply
multiply, (or divide as appropriate), the result of the formula by 100:

=MOD(D2-C2,10^LEN(D2))*100


That's great thanks.

I'd love to be able to combine that with Daddylonglegs formula which works
out the last reading

=MOD(LOOKUP(100000,C2:IV2)-LOOKUP(100000,C2:IV2,B2:IU2),100000)


Is that possible ?

Thank You

Hi Tracey.

I think that it is impertinent to change someone else's formula but as Daddy
still does not seem to be around and with my apologies to Daddy, try:

Insert a new Column B and enter the multiplier in it: 1 for no multiplier,
10 for x10 etc and 0.1 for divide by 10 etc.

Then change the formula to:

=MOD(LOOKUP(10^LEN(C2),D2:IV2)-LOOKUP(10^LEN(C2),D2:IV2,C2:IV2),10^LEN(C2))*B10

as before the *number* (or at least C2) must be text so that leading zeros
count as real characters

One caveat, if both figures are the same the formula will return zero and if
the first reading is smaller than the second it will return an error of the
capacity of the meter

Once again my apologies to Daddy

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Maria Tracey" wrote in message
...
"Sandy Mann" wrote in message
...
Hi Tracy,

If by Multiplier you mean it says on the meter " x 100 " then simply
multiply, (or divide as appropriate), the result of the formula by 100:

=MOD(D2-C2,10^LEN(D2))*100


That's great thanks.

I'd love to be able to combine that with Daddylonglegs formula which works
out the last reading

=MOD(LOOKUP(100000,C2:IV2)-LOOKUP(100000,C2:IV2,B2:IU2),100000)


Is that possible ?

Thank You

"Sandy Mann" wrote in message
...
Hi Tracey.

I think that it is impertinent to change someone else's formula but as
Daddy still does not seem to be around and with my apologies to Daddy,
try:

Insert a new Column B and enter the multiplier in it: 1 for no multiplier,
10 for x10 etc and 0.1 for divide by 10 etc.

Then change the formula to:

=MOD(LOOKUP(10^LEN(C2),D2:IV2)-LOOKUP(10^LEN(C2),D2:IV2,C2:IV2),10^LEN(C2))*B10

as before the *number* (or at least C2) must be text so that leading zeros
count as real characters

One caveat, if both figures are the same the formula will return zero and
if the first reading is smaller than the second it will return an error of
the capacity of the meter

Once again my apologies to Daddy

--
HTH

Can't get this to work sorry

:-(

Am I missing something ?

Should B10 be B2 ?

I changed that but still it didnt work.

"Maria Tracey" wrote in message
...

Should B10 be B2 ?

Yes it should - you are better than I am! g

As to why it does not work - what do you mean by does not work? do you get
a zero returned? nothing? or a wrong answer?

In my limited test I entered the following:

B2 10
C2 123456
D2 234567
E2 345678

Note that C2 must have a full length of digits or if it is less - for
example 123 - must be text so that it will show as 000123. Formatting the
number 123 to show three leading zeros will not suffice because Excel still
will just see a three digit number - the zeros are just formatting and are
not real.

The formula then returned 1111110 which is equivalent to (E2-D2)*10 which is
correct.

I then added:

F2 987654 and got an answer of 641976 which again is correct.

Next I added:

G2 123

Note that as C2 is returning the correct number of digits all the other
cells do not need to because the formula only references C2 in 10^len(C2)

the formula then returned 124690 which is equivalent to (1000000-G2+F2)*10
which again is correct.



--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Maria Tracey" wrote in message
...

"Sandy Mann" wrote in message
...
Hi Tracey.

I think that it is impertinent to change someone else's formula but as
Daddy still does not seem to be around and with my apologies to Daddy,
try:

Insert a new Column B and enter the multiplier in it: 1 for no
multiplier, 10 for x10 etc and 0.1 for divide by 10 etc.

Then change the formula to:

=MOD(LOOKUP(10^LEN(C2),D2:IV2)-LOOKUP(10^LEN(C2),D2:IV2,C2:IV2),10^LEN(C2))*B10

as before the *number* (or at least C2) must be text so that leading
zeros count as real characters

One caveat, if both figures are the same the formula will return zero and
if the first reading is smaller than the second it will return an error
of the capacity of the meter

Once again my apologies to Daddy

--
HTH

Can't get this to work sorry

:-(

Am I missing something ?

Should B10 be B2 ?

I changed that but still it didnt work.

"Sandy Mann" wrote in message
...
"Maria Tracey" wrote in message
...

Should B10 be B2 ?

Yes it should - you are better than I am! g

As to why it does not work - what do you mean by does not work? do you
get a zero returned? nothing? or a wrong answer?

Must have been the formatting I think! g

But how do I get round the two (improbable) successive equal readings or
where this months reading is smaller than last months reading because the
dials have gone full circle ?

Thanks

You're so patient !

But how do I get round the two (improbable) successive equal readings or
where this months reading is smaller than last months reading because the
dials have gone full circle ?

I think that it you would have to tell Excel that the reading had gone round
the clock by - say - putting any entry in the cell above the second reading
cell - in the case of my example Row 1.

Change the formula to:

=MOD(LOOKUP(10^LEN(C2),D2:IV2)-LOOKUP(10^LEN(C2),D2:IV2,C2:IV2),10^LEN(C2))+((LOO KUP(10^LEN(C2),C2:IV2,C1:IV1)"")*10^LEN(C2))*B2

If we now continue on with the same test and put 123 in H2 as well as G2,
the formula will return 0. If we then *tell* excel that the meter has
turned round by putting - say an R in H1 then the formula will then return
10000000. Similarly if we had entered 130 in H2 then we would have got
10000007

However, how would you know that the meter had turned round and past itself
again?

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Maria Tracey" wrote in message
...
"Sandy Mann" wrote in message
...
"Maria Tracey" wrote in message
...

Should B10 be B2 ?

Yes it should - you are better than I am! g

As to why it does not work - what do you mean by does not work? do you
get a zero returned? nothing? or a wrong answer?

Must have been the formatting I think! g

But how do I get round the two (improbable) successive equal readings or
where this months reading is smaller than last months reading because the
dials have gone full circle ?

Thanks

You're so patient !

Maria,

In my first response to you I suggested using one sheet for each meter,
with some header rows to store data for that meter, eg location,
description etc. You could use these header rows to record the number
of dials on the meter, as well as the multiplier for that meter. These
would always be in the same location, eg cells B3 and B4, and so you
could make use of them through absolute addresses in the formulae which
follow.

If you have 5 dials, then the meter clocks round when it reaches 99999
(or 1 less than 10 to the power 5). With 6 dials it wraps at 10 to the
power 6 etc. As you are planning to read the meters every month, then a
lower later reading implies that the meter has wrapped around, as you
won't have estimated readings which subsequently need correcting back.
Thus, your formula can take account of the wrapping and, if you can
refer to the number of dials, you can correct a negative reading by
adding back in 10 raised to the power (number of dials). The formula
from Sandy seems to be getting very complex.

Hope this helps.

Pete

Pete_UK wrote:
Maria,

In my first response to you I suggested using one sheet for each meter,
with some header rows to store data for that meter, eg location,
description etc. You could use these header rows to record the number
of dials on the meter, as well as the multiplier for that meter. These
would always be in the same location, eg cells B3 and B4, and so you
could make use of them through absolute addresses in the formulae which
follow.

If you have 5 dials, then the meter clocks round when it reaches 99999
(or 1 less than 10 to the power 5). With 6 dials it wraps at 10 to the
power 6 etc. As you are planning to read the meters every month, then a
lower later reading implies that the meter has wrapped around, as you
won't have estimated readings which subsequently need correcting back.
Thus, your formula can take account of the wrapping and, if you can
refer to the number of dials, you can correct a negative reading by
adding back in 10 raised to the power (number of dials). The formula
from Sandy seems to be getting very complex.

Hope this helps.

Pete

I had noticed *empty* posts like the one above before and wondered why. I
posted a reply this morning at work through Google and all that has shown up
is Pete's original post. I must have done something wrong I suppose.

Anyway what I posted was:

"Pete_UK" wrote in message
oups.com...

adding back in 10 raised to the power (number of dials). The formula
from Sandy seems to be getting very complex.

It's would not be so bad if it was just complex but it is wrong to boot!

What I should have posted was:

=MOD(LOOKUP(10^LEN(C2),D2:IV2)-LOOKUP(10^LEN(C2),D2:IV2,C2:IV2),10^LEN(C2))*B2+(( LOOKUP(10^LEN(C2),C2:IV2,C1:IV1)"")*10^LEN(C2))

But I would think that if there was any chance of the meter turning over
more than its capacity then Maria should be looking at replacing the meter
with one of greater capacity.

--

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


wrote in message
ups.com...

Pete_UK wrote:
Maria,

In my first response to you I suggested using one sheet for each meter,
with some header rows to store data for that meter, eg location,
description etc. You could use these header rows to record the number
of dials on the meter, as well as the multiplier for that meter. These
would always be in the same location, eg cells B3 and B4, and so you
could make use of them through absolute addresses in the formulae which
follow.

If you have 5 dials, then the meter clocks round when it reaches 99999
(or 1 less than 10 to the power 5). With 6 dials it wraps at 10 to the
power 6 etc. As you are planning to read the meters every month, then a
lower later reading implies that the meter has wrapped around, as you
won't have estimated readings which subsequently need correcting back.
Thus, your formula can take account of the wrapping and, if you can
refer to the number of dials, you can correct a negative reading by
adding back in 10 raised to the power (number of dials). The formula
from Sandy seems to be getting very complex.

Hope this helps.

Pete