mod equation

  • Thread starter Thread starter ala
  • Start date Start date
Hi,

If you format the result to 15 decimals (the limit of excel's precision)
then the answer will be returned correctly.

Take a look here http://support.microsoft.com/kb/214118

Then do a google search on 'Excel and floating point arithmetic'
or something similar, for a million other discussions on this point.

HTH
Martin
 
All,

Someday when everyone understands computers, I suppose. It is supprising
how many times this basic question arises.

In the past MOD had some issues, other than this one, does anyone know if
those have been fixed?
 
Hi,

I started at the top and worked down, now I see what you were refering to!

Maybe the answer will change? My wife uses this technique - if I don't give
her the answer she want the first time, she asks the question again....
 
Gauss's original definition of mod only applied to integers
http://en.wikipedia.org/wiki/Modulo
which remains the only domain in which computers can use it with full
accuracy unless they have infinite precision (only possible as symbolic
arithmetic, which is outside the scope of Excel).

mod(120,24) returns zero as expected.

The IEEE standard double precision binary approximation to 2.4 is
2.399999999999999911182158029987476766109466552734375, and
12-5*2.399999999999999911182158029987476766109466552734375
is 1/2251799813685248, which Excel's mod(12,2.4) correctly reports as
4.44089209850063E-16

The interesting aspect of this is that MS apparently went to great lengths
to improve the accuracy of the result from mod with floating point numbers.
In order to get 4.44089209850063E-16, mod must either use more that double
precision for intermediate calculations, or else must sequentially remove
powers of 2 to try to reduce rounding, as in
=((12-4*2.4)-2.4)

Jerry
 
Hi Jerry,

Does that mean that, provided you only use integers,
the MOD function in excel will not fail in any situation?

Thanks
Martin
 
Gauss's original definition of mod only applied to integers
http://en.wikipedia.org/wiki/Modulo
which remains the only domain in which computers can use it with full
accuracy unless they have infinite precision (only possible as symbolic
arithmetic, which is outside the scope of Excel).
Click to expand...
Infinite precision requires a data type with infinite length thus can never be
achieved in a finite world.
 
Hi Jerry,

Does that mean that, provided you only use integers,
the MOD function in excel will not fail in any situation?
Click to expand...
I do not think it is entirely fair to say MOD fails. It works exactly as
expected. The failure is that of understanding the limitations of floating point
arithmetic. It has always (and always will be) necessary to take account of the
precision of the arithmetic. In most cases it does not matter such as when
calculation percentage increase etc. In other cases it is vital. For example if
you calculate the intersection of two straight lines the algebra is quite simple
and you can determine the x ordinate to great precision. However if one of the
vectors is vertical the calculation of the y ordinate become a function of
x/zero (i.e. infinite). If you test for zero you will sometimes find that
vertical in not exact and there is a single bit error due to rounding. This can
result is a y answer on the other side of the universe. Understanding the
limitations is vital to writing code that always returns a sane answer.
 
AFAIK, if MOD returns a numeric answer with integer intputs, that result will
be correct.

The caveat about MOD returning a numeric answer is because for
number = i*divisor+j
if i >= 2^28 = 268435456, then MOD will return #NUM! instead of a number. I
have never seen a good explanation for this code limitation that has no
obvious basis in any numeric representation limitations.

Jerry
 
Dave Mills said:
Infinite precision requires a data type with infinite length thus can never
be
achieved in a finite world.
Click to expand...
....

Add a blank line between your quotes and your response to improve
readability.

Jerry should have said arbitrary precision, which means finite but possibly
orders of magnitude more decimal or binary places of precision. At some
point arbitrary precision needs to stop, but it's possible to achieve much
higher precision.
 
MartinW said:
Thanks Jerry,

2^28 is way past anything I will ever require.
Click to expand...
....

Possibly, but if for some reason you wanted to separate either US social
security numbers or telephone numbers stored as numbers into even and odd,
you can't use MOD to do it reliably.
 
Hi Harlan,

Yes, I see what you mean. So rather than thinking of the limit
as 2^28, the safest way is to think of the limit as 8 digits.

Martin
 
If you format the result to 15 decimals (the limit of excel's precision)
then the answer will be returned correctly.
Click to expand...

Formatting alters the appearance, but not the underlying value. In
this case, for example, if the MOD formula is in A1, then the formula
=(A1=0) returns FALSE, even if A1 is formatted so that it appears to
be zero.
 
You don't say Joe!!

It was meant as a visual example only.
Expecting that the OP would explore
with different decimal places to try
and sort out what is happening.


If you format the result to 15 decimals (the limit of excel's precision)
then the answer will be returned correctly.
Click to expand...

Formatting alters the appearance, but not the underlying value. In
this case, for example, if the MOD formula is in A1, then the formula
=(A1=0) returns FALSE, even if A1 is formatted so that it appears to
be zero.
 
Jerry W. Lewis said:
The interesting aspect of this is that MS apparently
went to great lengths to improve the accuracy of the
result from mod with floating point numbers. In order
to get 4.44089209850063E-16, mod must either use
more that double precision for intermediate calculations
Click to expand...

Right. But isn't that just a straight-forward implementation in a language
compiler that keeps the intermediate results in the FP registers (80 bits on
Intel-compatible processors)?

For example, the first VBA implementation below has the same non-zero result
as Excel's MOD function, whereas the second VBA implementation results in
exactly zero. As you indicated, in the second case, the only difference is
putting the intermediate computation into a 64-bit double.


Function mymod1(numer As Double, denom As Double)
mymod1 = numer - denom * Int(numer / denom)
End Function


Function mymod2(numer As Double, denom As Double)
Dim x As Double
x = denom * Int(numer / denom)
mymod2 = numer - x
End Function
 
AFAIK no MS compilers support 10-byte extended precision. VBA has only
limited indirect support for extended precision intermediate storage within a
single expression where all variables are explicitly declared double.

Jerry
 
Back
Top