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
|
|||
|
|||
Significant digit causing wrong value?
I have been using a simple IF formula w/o any problem for several weeks until
now. The IF statement just calculates the amount of product from a meter every week. This simple formula compares the 1st of the week vs the last of the week readings and even if this meter rolls over this IF statement calculates out the correct amount of product. IF(Z15Y15,100000000-Y15+Z15,Z15-Y15) Y=1st of week reading & Z=Last of week reading. The meter goes up to 99,999,999 units. Problem developed that caused the IF product results to be backwards. After noticing this I flipped the to and it worked correctly to my surprise - but how? After trying Cell Format options w/o any luck I tried placing a zero in front of the "Y" number when typing it in with Cell Format set on Numbers w/o decimals (sometimes the meter has just rolled over and the number is small w/one or more zeros in the ten million place and downward in value). I now keep the cell in Text format and place the zeros in (ie: 00012345) and this works fine as well but why has this issue just begun presenting itself? There has been many same type scenarios in the past w/o any issue. Also the file is always renewed every week using the "Save as" option and just renamed. |
#2
|
|||
|
|||
Significant digit causing wrong value?
Several things going on here.
First, to address your main question, significan digits is not the problem. XL can handle 15 sig digits, and your meter only goes up to 8. Second, by changing the cell format to Text, the logic check of: Z15Y15 where Z is text and Y is a number, will ALWAYS result in true. While this may give you the correct result, because XL is nice enough to convert the text "00050" into the number 50 and then subtract, its not really a good logic check. Unfortunately, the real problem here is undefined, since you only wrote "Problem developed that caused the IF product results to be backwards." While this may be true, w/o examples of your data, its harder to diagnose what went wrong. If Y & Z are formatted as numbers, and data is input as numbers, your formula could be: =Z15-IF(Z15Y15,Y15-100000000,Y15) -- Best Regards, Luke M "Eiyore" wrote in message ... I have been using a simple IF formula w/o any problem for several weeks until now. The IF statement just calculates the amount of product from a meter every week. This simple formula compares the 1st of the week vs the last of the week readings and even if this meter rolls over this IF statement calculates out the correct amount of product. IF(Z15Y15,100000000-Y15+Z15,Z15-Y15) Y=1st of week reading & Z=Last of week reading. The meter goes up to 99,999,999 units. Problem developed that caused the IF product results to be backwards. After noticing this I flipped the to and it worked correctly to my surprise - but how? After trying Cell Format options w/o any luck I tried placing a zero in front of the "Y" number when typing it in with Cell Format set on Numbers w/o decimals (sometimes the meter has just rolled over and the number is small w/one or more zeros in the ten million place and downward in value). I now keep the cell in Text format and place the zeros in (ie: 00012345) and this works fine as well but why has this issue just begun presenting itself? There has been many same type scenarios in the past w/o any issue. Also the file is always renewed every week using the "Save as" option and just renamed. |
#3
|
|||
|
|||
Significant digit causing wrong value?
"Eiyore" wrote:
After noticing this I flipped the to and it worked correctly to my surprise - but how? My suspicion is that you inadvertently mixed numeric and text data. Note that when comparing numbertext, the result is always TRUE even if the text appears to be numerically less. But in an arithmetic expression like number-text, the text will be converted to the correct number. For example, if Y1 contains ="00123" and Z1 contain =200 (both formatted as General): =IF(Z1Y1,1000000-Y1+Z1,Z1-Y1) results in 1000077, which might surprise you. I now keep the cell in Text format and place the zeros in (ie: 00012345) and this works fine as well but why has this issue just begun presenting itself? I presume you were careful to keep all data either all text or all numeric before. IMHO, the better solution is enter the data as numeric. If you want leading zeros, use the Custom format 00000000 (8 zeros). ----- original message ----- "Eiyore" wrote: I have been using a simple IF formula w/o any problem for several weeks until now. The IF statement just calculates the amount of product from a meter every week. This simple formula compares the 1st of the week vs the last of the week readings and even if this meter rolls over this IF statement calculates out the correct amount of product. IF(Z15Y15,100000000-Y15+Z15,Z15-Y15) Y=1st of week reading & Z=Last of week reading. The meter goes up to 99,999,999 units. Problem developed that caused the IF product results to be backwards. After noticing this I flipped the to and it worked correctly to my surprise - but how? After trying Cell Format options w/o any luck I tried placing a zero in front of the "Y" number when typing it in with Cell Format set on Numbers w/o decimals (sometimes the meter has just rolled over and the number is small w/one or more zeros in the ten million place and downward in value). I now keep the cell in Text format and place the zeros in (ie: 00012345) and this works fine as well but why has this issue just begun presenting itself? There has been many same type scenarios in the past w/o any issue. Also the file is always renewed every week using the "Save as" option and just renamed. |
Thread Tools | |
Display Modes | |
|
|