Meter Readings

[Maria Tracey]

Hello

I've been tasked with creating a spreadsheet that records the monthly
electricity meter readings where I work.

It's a big site with aboout 20 different meters to read around the site once
a month.
(The meters measure Kilo-Watt Hours) KWH
Some of the meters go right around the dials more than once so if say the
meter read 61567 one month, then the next month it could read 00123.
I can't then just subtract past reading from present reading because it
won't reflect the number of KWH used.
Each dial counts 0 to 9 for each digit.

The true KWH used in this example would be 00000-61567 = 38433 then
38433+00123 = 38556 KWH
It would not be 00123-38433 = -61444 KWH

From each meter reading I need to work out the total KWH used each month.

How do I go about creating a spreadsheet that the user can enter this months
figures (present) and last months figures which were then present now become
past figures so I can do the calculation.

I need to be able to use last months figures and this months figures on a
monthly basis to do the calculations.

Sorry it's long and confusing !!!

Any help much appreciated

Thank You

 

[Sandy Mann]

Maria,

Try something like:

=IF(G2<G1,100000-G1+G2,G2-G1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Pete_UK]

Assume you use row 1 for headings. You can record a date in column A
and the meter reading in column C. That allows you to have a "Days" in
column B, with a formula like:

=IF(A3="",0,A3-A2)

and formatted as a number with 0 dp. In column D you can obtain the
difference between the current reading and the one before it with a
formula like:

=IF(C3="",0,C3-C2)

This should be entered in D3, as row 2 will be for the opening reading.
To overcome the problem of the meter wrapping round, you can use this
formula in E3 for the actual kWh:

=IF(D3<0,D3+100000,D3)

You can copy the formulae in B3, D3 and E3 down for as many months as
you expect to do this for.

Once you have set this up as you would like (you would probably use
more header rows so that you can record details of the meter, and
location etc), you can then copy the sheet so that you have one sheet
for each meter. If you wish, you can summarise the information from
each sheet onto a master sheet.

Hope this helps.

Pete

 

[Maria Tracey]

Assume you use row 1 for headings. You can record a date in column A
and the meter reading in column C. That allows you to have a "Days" in
column B, with a formula like:

=IF(A3="",0,A3-A2)

and formatted as a number with 0 dp. In column D you can obtain the
difference between the current reading and the one before it with a
formula like:

=IF(C3="",0,C3-C2)

This should be entered in D3, as row 2 will be for the opening reading.
To overcome the problem of the meter wrapping round, you can use this
formula in E3 for the actual kWh:

=IF(D3<0,D3+100000,D3)

You can copy the formulae in B3, D3 and E3 down for as many months as
you expect to do this for.

Once you have set this up as you would like (you would probably use
more header rows so that you can record details of the meter, and
location etc), you can then copy the sheet so that you have one sheet
for each meter. If you wish, you can summarise the information from
each sheet onto a master sheet.

Hope this helps.

Thank you

I think this looks like what I'm aiming for.

 

[daddylonglegs]

If you have this months meter reading in D2 and last months in C2 the
this formula will give the units used

=MOD(D2-C2,100000)

If you set up your spreadsheet with each meter on a separate row,the
you can have a meter identifier in column A and first date's reading
in column C, second date in D etc. (row 1 contains meter reading date)
In B2 copied down you could use this formula

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

which will automatically give you the units used for the last mont

 

[Sandy Mann]

"daddylonglegs" <[email protected]>
wrote in message

=MOD(D2-C2,100000)

I like that one daddylonglegs

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


"daddylonglegs" <[email protected]>
wrote in message

 

[Pete_UK]

Thanks for feeding back.

Pete

 

[Maria Tracey]

"daddylonglegs" <[email protected]>
wrote in message

If you have this months meter reading in D2 and last months in C2 then
this formula will give the units used

=MOD(D2-C2,100000)

If you set up your spreadsheet with each meter on a separate row,then
you can have a meter identifier in column A and first date's readings
in column C, second date in D etc. (row 1 contains meter reading date).
In B2 copied down you could use this formula

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

which will automatically give you the units used for the last month

Hi Daddy,

I've set the sheet up as you suggested and it works like a dream (I think !)

I've discovered though a few little quirks from the meter readings .......

Some of the meters have 5 dials hence 5 digits and some have 6 therefore 6
digits and there is also one with 7 digits.

How do I modify the formula to take into account those ?

Also, and this is a tricky one for me......

Some of the readings from the meters have a multiplier like times 100 or
times 1000 and another which is divide by 1000

So is it possible to enter the reading in its native format and have Excel
apply the correct times or divide for each one ?

Thanks for your help

Maria

 

[Sandy Mann]

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))


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Maria Tracey]

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]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Maria Tracey]

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]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Maria Tracey]

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

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]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Maria Tracey]

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 !

 

[Sandy Mann]

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))+((LOOKUP(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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Pete_UK]

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

 

[sandy.mann]

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

 

[Sandy Mann]

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:


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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk