<-- Return to Excel Statistics Guide
The Two Excel Modulo Functions
Keith Greiner
August 8, 2020
Modulo functions return the remainder from a division problem. For example, in the division of 10/3, the returned value is 3.33333333 and the remainder (modulus) is 0.3333333. Excel has two modulo functions: one is available in a spreadsheet, and the other is available in VBA. Unlike many functions, the spreadsheet function cannot be called from VBA. The differences would not be a problem, except that the two functions can return different values. My suggestion to Microsoft is that they fix the problem, but as August 21, 2017, they have not done so.
The spreadsheet function looks like a function you will find in other languages. Its syntax is...
Returned_Value = MOD(a,b)
...where a is the dividend, and b is the divisor.
In the example of 10 and 3, the returned value is 1.0.
In the example of 10.5 and 3, the returned value is 0.5.
The VBA function has a unique syntax that is...
Returned_Value = a MOD b...where a is the dividend and b is the divisor.
In the example of 10 and 3, the returned value is the integer 1.
In the example of 10.5 and 3, the returned value is the integer1.
Some projects need the modulus to include the decimal portion.
My solution to the problem is to use the following VBA code when seeking a modulus value in VBA.
If you use this function, be sure to check it to verify that it works correctly in your application.
Function Modulo(a, b) ' This VBA function replicates the Excel Worksheet MOD(a,b) function Modulo = a - b * Int(a / b)End Function-30-