Conflicting results in formulas?

[mikelee101]

Hello,
I have something that is confusing me greatly. Here's the condensed
version:

I have a formula in L5. I want to test to see if the result is an
integer.

In W4, I have the formula

=L5-INT(L5)<>0 Result given is TRUE

However, it should be an integer, so to test I put in cell W5

=L5-INT(L5) Result given is 0

To make sure that it wasn't something 10 or 15 decimal places out, I
entered the following in W6

=W5=0 Result given is TRUE

Just for giggles, in W7 I put

=L5=INT(L5) Result given is TRUE

So...what's wrong with the formula in W4 that it returns results that
are in direct conflict with the rest of them? Or is that one
potentially correct...?

If anyone has any insight, I'd be grateful.

XL03, WinXPPro SP2

Thanks,
Mike Lee
Coppell, TX

 

[Tyro]

What is your formula in L5 and what values are used by that formula, not
formatted, but actual?

Tyro

 

[mikelee101]

What is your formula in L5 and what values are used by that formula, not
formatted, but actual?



















- Show quoted text -

The formula in L5 is:

=SUMPRODUCT(--(month=MONTH(DATEVALUE($A5&" 1, 2008"))),--
(agent=VLOOKUP(A1,name.table,2,FALSE)))/(SUMPRODUCT((datapoint<>"")/
COUNTIF(datapoint,datapoint))-1)

where month is a named range containing months, and it's compared
against a text label in column A (January, February, etc.), agent is a
named range containing names which is compared against a "preferred
name" that's looked up from a table named name.table, and datapoint is
a named range that contains a description of what each row of data
contains (i.e. Calls Answered, Logged in Time, etc).

the purpose of the formula is to calculate how many days an agent
worked during the month. it accomplishes that by totaling up the
number of times that an agent appears in a given month, then divides
that by the number of unique datapoints. I'm not really sure how to
go about showing you the actual data, since all the function really
does is count things.

as far as the result of the formula, this is it to 20 decimal places:

6.00000000000000000000

however, i still get True when it calculates L5-INT(L5)<>0.

after i posted this, i decided to try one more thing, so i went with

=ROUND(L5,25)-INT(ROUND(L5,25))<>0

and i got FALSE for that one, so it's clearly a rounding problem
somewhere down the line. I just don't understand why those different
formulas that should(?) be the same thing give different results...

thanks,
mike

 

[Jerry W. Lewis]

Unfortunately, this inconsistency is intended by MS, and will occur anytime
that L5 is not an integer but differs from INT(L5) beyond the 15th
significant figure (such as =1+2^-52). Under those circumstances expressions
like =L5-INT(L5) and =L5=INT(L5) cannot be relied upon to accurately reflect
the value in L5. Instead you need to structure the formula such that the
subtraction or comparison is not the last operation, as in =(L5-INT(L5)) or
=L5-INT(L5)<>0.

As documented, Excel will display no more than 15 significant figures. If
you ask for more, you will get a result padded with meaningless zeros instead
of an accurate reflection of the cell contents, but you can indirectly
determine what is going on
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5

Almost all software does math in finite precision (the only alternative
would be symbolic math, which would be prohibitively slow in a large
spreadsheet). Under some circumstances, finite precision math necessarily
violates familiar mathematical rules such as x = a+x-a. For example, if you
were doing 4-figure decimal math, then
1+1/3-1 = 1+0.3333-1 = 1.333-1 = 0.333 <> 0.3333
Most people recognize that 1/3 has no finite decimal representation, and so
are not surprised by this, but people who have not thought about the binary
math underlying Excel and almost all other software are surprised when
similar things happen involving numbers like 0.1 (which has no finite binary
representation). In an effort to smooth this out (a technically impossible
challenge) MS introduced an "optimization" in Excel 97
http://support.microsoft.com/kb/78113
such that final subtractions of numbers that are equal to 15 decimal digits
will return zero, and final comparisons of numbers that are equal to 15
decimal digits will be declared equal. This will only be an improvement if
the small real differences only exist because of finite precision
approximations. The "optimization" cannot be extended beyond the final
operation, because it would result in increasing inaccuracy. Depending on
what calculations have taken place, this fuzz factor may be too much or may
not be enough--one size does not fit all. A better approach would have been
to educate users with guidelines on how to more effectively get the intended
results in finite precision, but there have been 4 subsequent Excel versions
with no indication that MS recognizes the need to rethink their approach, so
I won't hold my breath.

The classical discussion of the issues around finite precision floating
point arithmetic is Goldberg's article "What every computer scientist should
know about floating point arithmetic"; copies can be found many places on the
internet, such as
http://www.physics.ohio-state.edu/~dws/grouplinks/floating_point_math.pdf

Jerry

 

[Tyro]

I worked on an old mainframe computer called UNIVAC, designed in the 1940's.
Although it did bit by bit arithmetic it could add, subtract, multiply and
divide decimal numbers and produce the correct decimal answers. How far we
have come! Or, have we?

Tyro