If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
Meter Readings
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 |
#4
|
|||
|
|||
Meter Readings
"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. |
#5
|
|||
|
|||
Meter Readings
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 |
#6
|
|||
|
|||
Meter Readings
"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 |
#7
|
|||
|
|||
Meter Readings
Thanks for feeding back.
Pete |
#8
|
|||
|
|||
Meter Readings
"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 |
#9
|
|||
|
|||
Meter Readings
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 |
#10
|
|||
|
|||
Meter Readings
"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 ? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Select query - only one-to-many records? | AndyK | Running & Setting Up Queries | 4 | March 27th, 2006 05:52 AM |
Latest meter reading | Dan Johnson | Running & Setting Up Queries | 2 | April 25th, 2005 09:53 PM |
Need to create a progress meter while an Access module runs | Brent E | General Discussion | 5 | January 28th, 2005 11:25 PM |
query progress meter | Bob White | General Discussion | 8 | December 6th, 2004 11:57 AM |
Trying to get difference in Meter readings between rows | Mike S. | Running & Setting Up Queries | 2 | June 8th, 2004 07:05 PM |