Problem with the MOD function in excel?

Question

=MOD((15*(1,4-1));6)

returns 6,0 and

=MOD((15*(0,4));6)

returns 0,0

I would like the first MOD function to return 0 as well. Is there a reason why Excel behaves like this? Do you experience the same problem in excel 2010?

I could replace the function with a INT-test instead ( =(15*(1,4-1))-6*INT((15*(1,4-1))/6) ), but it would be nicer if MOD would work.

Office 2010 - I did not experience the same problem in office 2003

Thanks

Answer 1

joeu2004 wrote:

Third, JP is correct to be concerned about MOD with non-integers, due to the limitations of computer arithmetic [2].  But I believe Koeiegeloei's formula, as corrected above, ameliorates those concerns.

In the below link

http://msdn.microsoft.com/en-us/library/se0w9esz.aspx

Microsoft states:  If either number1 or number2 is a floating-point value, the floating-point remainder of the division is returned. The data type of the result is the smallest data type that can hold all possible values that result from division with the data types of number1 and number2.

Floating-Point Imprecision

When you work with floating-point numbers, remember that they do not always have a precise representation in memory. This could lead to unexpected results from certain operations, such as value comparison and theMod operator. For more information, see Troubleshooting Data Types (Visual Basic).

So, IMO this proves that MOD should not be used when an argument is not an integer.

=MOD(21,3;7,1) returns also 2^-49 or 1,77636E-15. As Dana stated, this is a -49th power of 2. Why does it always fails on this?

Answer 2

JP wrote:

Microsoft states:  If either number1 or number2 is a floating-point value, the floating-point remainder of the division is returned.

[....]

Floating-Point Imprecision

When you work with floating-point numbers, remember that they do not always have a precise representation in memory. This could lead to unexpected results from certain operations

[....]

So, IMO this proves that MOD should not be used when an argument is not an integer.

Amazing bit of (il)logic!  You quote a statement that describes how to use MOD with non-integers [1], then you conclude "this proves" that MOD should not be used with non-integers.

I can only assume you are referring to the caveat about floating-point imprecision.

But if "that proves" that MOD should be used with non-integers, the same (il)logic applies to simple arithmetic operations like additiion, subtraction, division and multiplication.

For example:  =(10.1 - 10 = 0.1) returns FALSE(!).

JP wrote:

=MOD(21,3;7,1) returns also 2^-49 or 1,77636E-15. As Dana stated, this is a -49th power of 2. Why does it always fails on this?

Because you are using constants? :-) :-)

Seriously, are you asking why the residual is "always" 2^-49?

It isn't.  MOD(5631.93, 208.59) is about 1.99E-13, exactly 7*2^-45.

Perhaps more befuddling and insightful:  in Excel 2010, MOD(279.4, 25.4) seems to return 25.4, even when formatted to display 15 significant digits.

A more careful comparison [2] -- MOD(279.4, 25.4)-25.4-0 -- reveals that the result is actually 25.4 - 7.1E-15, exactly 2^-47 less than the internal representation of 25.4.

The VBA functions below model 4 different ways to implement MOD.  The algorithms are identical.  The only difference is the precision of intermediate calculations.  Hopefully, the comments are self-explanatory.

---

I want to emphasize that the VBA implementations below are merely models.  I'm sure Excel uses a different computer language to implement MOD.  The Excel implementation might behave differently for some MOD parameters.

Function vbmod80(n As Double, d As Double) As Double

' all 80-bit FP arithmetic

' n = 279.4 (279.399999999999,9772626324556767940521240234375)

' d = 25.4  (25.3999999999999,9857891452847979962825775146484375)

' vbmod80 ~= d (< d), = 25.3999999999999,914734871708787977695465087890625

' different from XL2003 MOD(n,d)

' same as XL2010 MOD(n,d)

vbmod80 = n - d * Int(n / d)

End Function

Function vbmod6480(n As Double, d As Double) As Double

' mixed 80-bit and 64-bit FP arithmetic

' n = 279.4 (279.399999999999,9772626324556767940521240234375)

' d = 25.4  (25.3999999999999,9857891452847979962825775146484375)

' vbmod6480 ~= 0 (< 0!) = -0.00000000000000710542735760100,185871124267578125

' same as XL2003 MOD(n,d)

' different from XL2010 MOD(n,d)

Dim t As Double

t = n / d

vbmod6480 = n - d * Int(t)  ' 80-bit FP

End Function

Function vbmod8064(n As Double, d As Double) As Double

' mixed 80-bit and 64-bit FP arithmetic

' n = 279.4 (279.399999999999,9772626324556767940521240234375)

' d = 25.4  (25.3999999999999,9857891452847979962825775146484375)

' vbmod8064 ~= d (< d), = 25.3999999999999,772626324556767940521240234375

' different from XL2003, XL2010 MOD(n,d)

' different from vbmod80

Dim t As Double

t = d * Int(n / d)    ' 80-bit FP

vbmod8064 = n - t

End Function

Function vbmod64(n As Double, d As Double) As Double

' all 64-bit FP arithmetic

' n = 279.4 (279.399999999999,9772626324556767940521240234375)

' d = 25.4  (25.3999999999999,9857891452847979962825775146484375)

' vbmod64 = 0

' different from XL2003, XL2010 MOD(n,d)

' different from vbmod6480

' same as XL2003, XL2010 formula:  =n-d*INT(n/d)

Dim t As Double

t = n / d

t = Int(t)

t = d * t

vbmod64 = n - t

End Function

---

[1] Note that JP's excerpt is from a "man page" that explains how to use MOD with VB.  It does not apply to VBA Mod, which uses only integral operands.

[2] The redundant -0 is needed to work around the dubious "close to zero" heuristic described in KB 78113 (click here), whereby Excel arbitrarily and inconsistently replaces an infinitesimal result with zero.

Answer 3

Hi Joeu,

Thanks for the feedback.

Have you seen Bernard's question? I am also interested.

Hello Joeu

Any chance you could point me to the place where I can learn how to show, for example,

A7:  100.666666666666    100.666666666666,00349344662390649318695068359375

I did have a macro from Jerry Lewis at one tine

best wishes

Bernard

Answer 4

Hi Joeu,

Thanks for the feedback.

Have you seen Bernard's question? I am also interested.

Hello Joeu

Any chance you could point me to the place where I can learn how to show, for example,

A7:  100.666666666666    100.666666666666,00349344662390649318695068359375

I did have a macro from Jerry Lewis at one tine

best wishes

Bernard

Hi.  Here's something quick and dirty till you get a better program.  I too wrote a better one in the past, and can't find it at the moment.  I forgot that the one doesn't need to implement a full high precision division.  One can take advantage of the binary nature of the problem.

The gist of the problem is to add the appropriate values ie (1/2, 1/4, 1/8, etc)

There are lots of ways to implement the higher math involved.   This is just one of many ways.

I skipped the 7 digit binary value of 100 (1100100) and started at the fractional value at position 8.

Sub TestIt()

    Debug.Print BinToReal("1010101010101010101010101010101010101001111100")

End Sub

Function BinToReal(s As String) As String

    Dim H, L, UL, AL, AH, K, K5

    Dim J As Long

    H = CDec("1" + WorksheetFunction.Rept("0", 26))

    L = CDec(0)

    UL = H * 10

    AL = L

    AH = L

    K5 = 5 * H

    For J = 1 To Len(s)

        L = L / 2

        H = H / 2

        If Right(H, 2) = ".5" Then

            H = H - 0.5

            L = L + K5

        End If

        If Mid$(s, J, 1) = "1" Then

            AH = AH + H

            AL = AL + L

            If AL >= UL Then

                K = Int(AL / UL)

                AH = AH + K

                AL = AL - K * UL

            End If

        End If

    Next J

    BinToReal = Left$(CStr(AH) & CStr(AL), J - 1)

End Function

Returns the following, whos digits match Joeu's.

       6666666666660034934466239064931869506835937500

100.66666666666600349344662390649318695068359375

Always an interesting subject !  :>)

Dana DeLouis