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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
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 |