"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 number<text, 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(Z1<Y1,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(Z15<Y15,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.