If you are working in a financial organization or in an accounting firm or a school and teach mathematics and needs to find the nth Number and what is left after, one should know about MOD function.
This has been implemented in several tools and programming languages differently, here we are going to look at the Mod function in excel.
The Excel MOD function returns the remainder of two numbers after division. For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
Purpose:
Get the remainder from division
Return value:
The remainder
Syntax: =MOD (number, divisor)
Examples:
=MOD(10, 3) /// result 1
=MOD(29, 4) /// result 1
=MOD(36, 4) /// result 0
How the Modulus is derived:
How the Modulus is derived:
Find the Mod (remainder) of 22 divided by 4
Derivation => 22 – (5*4) = 2
5 is the multiplier that gives nearest number less than 22. The Multiplier number can also be derived by finding the quotient of both given number 22 divided by 4 = 5.
Below equation can also show the details:n-d*INT(n/d)
where n is number, d is divisor, and INT is the INT function. This can create some unexpected results because of the way that the INT function rounds negative numbers down, way from zero:=MOD(7,3) // returns 1=MOD(7,-3) // returns -2
MOD with negative numbers is implemented differently in different languages.
In excel use MOD function to derive the above. In a cell write =MOD(22,4) , this results in 2.
Mod of Negative Numbers
The result from MOD carries the same sign as divisor. If divisor is positive, the result from MOD is positive, if divisor is negative, the result from MOD is negative:=MOD(-3,2) // returns 1=MOD(3,-2) // returns -1=MOD(-3,-2) // returns -1
Derive Time from DateTime with MOD function
The MOD function can be used to extract the time value from an Excel date that includes time (sometimes called a datetime). With a datetime in A1, the formula below returns the time only:=MOD(A1,1) // return time only
=MOD(NOW(), 1) //results in 00-01-1900 17:16 (the result can also be formatted).
Large Numbers
With very large numbers, you may see the MOD function return a #NUM error. In that case, you can try an alternative version based on the INT function:
=number-(INT(number/divisor)*divisor)
MOD is often seen in formulas that deal with “every nth” value
MOD is useful for extracting the time from a date
MOD always returns a result in the same sign as the divisor.
MOD will return a #DIV/0! error if divisor is zero
To discard the remainder and keep the integer, see the QUOTIENT function.
