Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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(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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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(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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
Thanks for feeding back.
Pete |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 ? |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 ? |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
"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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
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 |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
"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. |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
"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. |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
"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 ! |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
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 ! |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
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 |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
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 |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
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 |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
"Sandy Mann" wrote in message ... 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. I couldn't get this one to work Sandy - sorry ! It just gives me a zero for an answer each time. The meters never go round more than once, quite often not going all the way round for many many months Thanks again. Tracey |
#22
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Meter Readings
Hi Maria,
Well......... If the meter is never going to go round more than once then I would just forget about the last formula I posted and just use the one that you did get to work which was: =MOD(LOOKUP(10^LEN(C2),D2:IV2)-LOOKUP(10^LEN(C2),D2:IV2,C2:IV2),10^LEN(C2))*B10 or use Pete's formula Remember that C2 needs to have the the full number of real characters even if some of them are leading zeros and B10 must have a number in it even if it is 1. If you still are having trouble then do post back. -- Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Maria Tracey" wrote in message ... "Sandy Mann" wrote in message ... 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. I couldn't get this one to work Sandy - sorry ! It just gives me a zero for an answer each time. The meters never go round more than once, quite often not going all the way round for many many months Thanks again. Tracey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for average recorded blood pressure readings in 1 column . | Excel Worksheet Functions | |||
compute insulin doseage with carb consumed & BS readings | Excel Worksheet Functions | |||
Daily water meter readings | Excel Worksheet Functions | |||
Chart to monitor Blood Pressure readings and date | Charts and Charting in Excel | |||
how do I display "meter square" symbol in a formula cell ? | Excel Worksheet Functions |