Meter Readings

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

Maria,

Try something like:

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Maria Tracey" wrote in message
...
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

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(D30,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

"Pete_UK" wrote in message
oups.com...
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(D30,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.

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=532820

"daddylonglegs"
wrote in message
news:daddylonglegs.2691am_1145009404.7613@excelfor um-nospam.com...

=MOD(D2-C2,100000)

I like that one daddylonglegs

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"daddylonglegs"
wrote in message
news:daddylonglegs.2691am_1145009404.7613@excelfor um-nospam.com...

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=532820

Thanks for feeding back.

Pete

"daddylonglegs"
wrote in message
news:daddylonglegs.2691am_1145009404.7613@excelfor um-nospam.com...

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=532820

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

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


with @tiscali.co.uk


"Maria Tracey" wrote in message
...
"daddylonglegs"
wrote in
message news:daddylonglegs.2691am_1145009404.7613@excelfor um-nospam.com...

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=532820

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" 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 ?