| Syntax |
| =MOD(number,divisor) |
| Purpose / Description |
| Returns the modulo or remainder of division, after dividing the number by divisor. |
| Arguments |
| Name |
Type |
Description |
| number |
number |
A constant, reference or nested expression that provides the number whose modulo is to be calculated. |
| divisor |
number |
A constant, reference or nested expression that provides the divisor by which number will be divided.. |
| Return Values |
| Type |
Specific Value |
Description |
| number |
|
The remainder after dividing number by divisor, and if the result is non-zero, it will have the same sign as divisor.. Effectively the result is equal to number - divisor * INT(number / divisor). |
| error |
#VALUE! |
If either number or divisor is not a number and cannot be coerced to a number. |
| error |
#NUM! |
If the ratio of number:divisor exceeds 1:250 |
| error |
|
Any errors referenced by arguments are returned due to propagation of error values. |
| Notes / Comments / Advice / Warnings |
| Note: |
Due to the way floating point numbers are implemented in the processors and Excel, small inaccuracies occur with decimal values |
| Note: |
Accepts multi-cell references for both arguments. |
| Note: |
The impact of the sign of the divisor can be summarised as follows:
| =MOD(10,3) |
Returns 1 |
10-3*3) → 10-9 |
INT(10/3) is 3 |
| =MOD(-10,3) |
Returns 2 |
-10-3*-4 → -10+12 |
INT(-10/3) is -4 |
| =MOD(10,-3) |
Returns -2 |
10--3*-4 → 10-12 |
INT(10/-3) is -4 |
| =MOD(-10,-3) |
Returns -1 |
-10--3*3& → -10+9 |
INT(-10/-3) is 3 |
|
| Comment: |
The minor inaccuracies mentioned in the note above are no worse than any other imprecision we deal with in the floating point calculations in Excel, however, some techniques using MOD, rely on accurate remainders. This will generally not be an issue where the number and divisor are whole numbers, due to optimisations built into Excel. |
| Advice: |
This function is useful where cyclic forecasting is required, although the advent of dynamic arrays has opened some other possibilities. Both techniques are demonstrated in our courses. |
| Examples: |
Open / download sample workbook: MOD.xlsx |
| |