Excel MOD function is a mathematical Excel function that calculates the remainder when one number is divided by another number. It is a useful function for performing calculations in a variety of applications, such as finance, engineering, and science. With the MOD function, users can easily determine the remainder when dividing numbers, which is particularly useful when working with large datasets or complex formulas.
Excel MOD Function Summary
The MOD function in Excel calculates the remainder of a division operation between a dividend and a divisor. Also known as the modulo operation, MOD is a useful tool in mathematical calculations and can help simplify complex formulas.
MOD Function Purpose
The purpose of the Excel MOD function is to calculate the remainder when one number is divided by another number.
MOD Function Arguments
The Excel MOD function takes two arguments:
- Number: This is the number that you want to divide.
- Divisor: This is the number that you want to divide the number by.
MOD Function Return value
The Excel MOD function returns the remainder when the number is divided by the divisor.
MOD Function Syntax
=MOD(Number, Divisor)Excel MOD Function Examples
The MOD function is a useful tool for finding the remainder after division. In this section, we will explore several examples that demonstrate how the MOD function can be used in practical applications.
Calculating the remainder
Suppose you want to calculate the remainder when 10 is divided by 3. In this case, the formula would be:
=MOD(10,3)
The result of this formula is 1, since 10 divided by 3 leaves a remainder of 1.
Testing for even or odd numbers
The MOD function can be used to determine whether a number is even or odd. For example:
=IF(MOD(A2,2)=0,"Even","Odd")
This formula tests whether the number in cell A2 is even or odd, and returns “Even” if the number is even, and “Odd” if it is odd.
Using MOD with SUMPRODUCT function
You can use the MOD function with the SUMPRODUCT function to sum the values in a range based on a specific condition.
For example:
=SUMPRODUCT((MOD(A1:A10,3)=0)*A1:A10)
The formula as we see sum’s all the values in the range A1:A10 that are divisible by 3.
Mod formula to sum every Nth row or column
Suppose you have a column of numbers in A1:A10, and you want to sum every other row, starting with the first row. You can use the following formula:
=SUMIF(MOD(ROW(A1:A10),2),1,A1:A10)The ROW(A1:A10) function returns an array of row numbers for the range A1:A10. The MOD function then calculates the remainder when each row number is divided by 2. This gives an array of 1s and 0s indicating whether the row number is odd or even.
The SUMIF function then sums the values in the range A1:A10, but only includes the values corresponding to the 1s in the MOD array. Since we started with the first row, the formula sums every other row.
You can modify this formula to sum every Nth row by changing the 2 in the MOD function to the desired interval. For example, to sum every third row starting with the second row, you can use the following formula:
=SUMIF(MOD(ROW(A2:A10)-1,3),0,A2:A10) Here, we subtracted 1 from the row numbers before applying the MOD function, so that the first row is considered a multiple of 3 and included in the sum. The formula then sums every third row.
MOD Function Notes
- The MOD function returns the same sign as the divisor, regardless of the sign of the number being divided.
- If the divisor is 0, the MOD function returns a #DIV/0! error.