Excel error on function MOD

Question

I want to input this formula in my excel file (VBA preferably). But from some reason I get an error when I put this function im my formula bar:

#NUM! - Number Error

My function is:

=98-mod(2000000000123456*100;97))

But When I enter just 10 digits, then it works. I need to work with 16 digits. I am trying basically to validate number based on ISO 7064 Mod 97,10 formula Thanks in advance.

Thanks pepople, for your effort, but I've tried code that @Robyn wrote, and I get overflow error. Here is my code (I will write just Function section): 'Note: An entering paramters are: 3259300700853850 and 97

 Function DblMod(Dividend, Divisor)
' Declare two double precision variables
Dim D1 As Double
Dim D2 As Double
' Copy function arguments to local variables
D1 = Dividend
D2 = Divisor
DblMod = D1 Mod D2
End Function

Answer 1

As has been written, Excel has a limitation of 15 digits precision. So when you enter your formula above, your 16-digit number will be altered:

2000000000123456 becomes 2000000000123460

One way to do work around this limitation involves using VBA and also changing your formula.

Instead of writing 16 digit number * 100, enter the entire value as a string:

"200000000012345600"

eg:

=98-xMOD("200000000012345600";97)

Then use this UDF which makes use of the increased precision afforded by VBA's Decimal data type:

Option Explicit
Function xMOD(Num As Variant, Div As Long)
    Dim x As Variant, y As Variant

x = CDec(Num)
y = CDec(Div)

xMOD = x - Int(x / y) * y

End Function

If you will be entering your 16 digit number (as a string) in some cell, and always multiplying by 100, you can use the following formula:

=98-xMOD(C1&"00";97)

Or, you could just enter the 18 digit number, as a string into some cell and reference it in your formula, omitting the &"00" part.

Another option, if you can find it, would be an unsupported free add-in called xNumbers. The latest version 6.0 still works with Excel 2016, although the integration with the ribbon is non-existent, so some of the configuration options cannot be used.

EDIT Since you wrote that you were unable to get this UDF to work in your system, I will post a screen shot showing that it does work in mine, and also that it agrees with the results you have posted in your bounty description. (xIntMod from xNumbers also returns the same results)

Answer 2

For numbers with abs() less than or equal to 2,147,483,647

This is a known issue per the Microsoft Support article XL: MOD() Function Returns #NUM! Error Value.

Note that this article is quite dated - so this might not be the true root cause in your instance.

The article suggests the following as a workaround:

Instead of using this formula =MOD(J13,K13) use this formula: =J13-(INT(J13/K13)*K13)

Alternatively, this article suggests a VBA equivalent:

Function DblMod(Dividend, Divisor)
    ' Declare two double precision variables
    Dim D1 As Double
    Dim D2 As Double

    ' Copy function arguments to local variables
    D1 = Dividend
    D2 = Divisor

    DblMod = D1 Mod D2
End Function

For numbers with abs() greater than 2,147,483,647

In this scenario, the VBA MOD function overflows with Run Time Error '6': Overflow. This is addressed in Microsoft Support article PRB: "Overflow" with Integer Division and MOD Operator. The article suggests the below solution in VBA, which I have tested works as expected with the OP's given numbers:

Sub Main()
    Call DblMod2(3259300700853850, 97) 
    ' returns 46, which matches the Big Integer Calculator at http://www.javascripter.net/math/calculators/100digitbigintcalculator.htm suggested by Ron
End Sub

Function DblMod2(Dividend, Divisor)

    Dim dblX As Double
    Dim dblY As Double
    dblX = Dividend               ' numerator
    dblY = Divisor                ' denominator

    ' round off the numerator and denominator (ensure number is .0)
    dblX = Int(dblX + 0.5)
    dblY = Int(dblY + 0.5)

    ' Emulate integer division with: Fix(dblX / dblY)
    ' Emulate modulo arithmetic with: dblX - (dblY * Fix(dblX / dblY))
    DblMod2 = dblX - (dblY * Fix(dblX / dblY))
    Debug.Print DblMod2
End Function

Answer 3

There is an alternate for the MOD function here

a - (b * (a / b))

=98-((2000000000123450*100)-(97*((2000000000123450*100)/97)))

---

EDIT 1

Try with below, it will return 46

Function DblMod(Dividend, Divisor)
    ' Declare two double precision variables
    Dim D1 As Double
    Dim D2 As Double
    Dim D3 As Double
    Dim str1
    Dim Str2
    ' Copy function arguments to local variables
    D1 = CDbl(Dividend)
    D2 = Divisor
    DblMod = CDec(D1) - (Round(CDec(D1) / D2) * D2)
End Function

---

Sub test()
    test = DblMod("3259300700853850", 97)
End Sub

Answer 4

Instead of dealing with very big numbers, just split them up like this:

Public Function ExtMod(Dividend As String, Divisor As Long) As Double
  Dim i As Long, j As Double
  For i = 1 To Len(Dividend)
    If Not IsNumeric(Mid(Dividend, i, 1)) Then
      j = j & Mid(Dividend, i)
      Exit For
    End If
    j = j * 10 + Mid(Dividend, i, 1)
    While j >= Divisor
      j = j - Divisor
    Wend
  Next
  ExtMod = j
End Function

Also works with decimals and locale should not matter. Only limitation is is the max length of strings which you probably never will hit.

Tested it with "123456789012345678901234567890.3241543" and 643 which is 435.3241543.

If you have any questions, just ask ;)