In Excel, sometimes you need to deal with some complex tasks like to sum or highlight cells that have even or odd numbers, sum every Nth rows or column in a range, validate cells to enter numbers with even or odd or without fraction and many more. To solve these tricky problems you need to use Excel MOD function. Though Excel MOD function is basically designed to get the division remainder, it is far more useful to solve many complex problems like above while working with data.
Excel MOD function returns the remainder after a number is divided by a divisor. In Excel, it is categorized as a Math & Trig Function and it returns a numeric value.
Syntax of MOD function is:
MOD( number, divisor )
Number: A numeric value whose remainder is needed.
Divisor: The number used to divide the number parameter.
- If the divisor is 0, then it will return the #DIV/0 error because a number cannot be divided by 0.
- If any of two arguments is text value then it will return #VALUE error because it handles only numeric values.
For example, if you divide the numbers by divisors, then it will return remainder values by using MOD function as follows;
It is very rare that MOD function is used to get the only remainder of division in Excel. MOD function is quite often used in formulas to make calculations based on the remainder of the divisor in Excel. In this article, you will learn how to use MOD function in Excel to solve various advanced problems to reach at solutions easily.
Count cells that contain odd or even numbers
Excel does not have any built-in function to perform such count but MOD function can do this in the formula using MOD and SUMPRODUCT functions. As you know that if a value is divided by 2 and remainder is 0 then it is even value, but if the remainder is 1 then the value is odd. Using MOD function you can perform this check that which cell has even or odd value in a selected range of cells, and SUMPRODUCT function counts the total cells where this condition is TRUE.
Suppose you have numbers in a range of cells A2:A8 and you need to count cell that have Even or Odd numbers using the following formulas;
Count cells with Even numbers:
Count cells containing Odd numbers:
Here, MOD function divides each cell’s value in range by 2, and if remainder in the result is 0 then it is even valued and if the remainder is 1 then it is an odd number. Excel returns a series of TRUE and FALSE where these logical tests are met or not in the above formulas. Double dashes (–) outside the MOD function converts series of TRUEs and FALSEs into 1s and 0s and SUMPRODUCT function sums this series of 1s and 0s to give you the total count of cells.
Sum every other row or column
Based on even and odd numbers logic, you can sum every other row or column in a selected range of cells using MOD, ROW or COLUMN and SUMPRODUCT functions. In this case, you can sum every even or odd row or column number in the selected range of cells.
Suppose you have a range of cells with a number and you want to sum every even or odd row then formulas will be as follows;
Sum every Even row:
Sum every Odd row:
Here, MOD function will perform check where row number in the selected range is even or odd, and SUMPRODUCT function will sum the values of even or odd row numbers in the above formulas.
Sum every Nth row or column
There is no built-in function in Excel to sum values of every 2nd, 3rd or 4th etc. row or column in a range. But, using MOD function with the combination of ROW or COLUMN and SUMPRODUCT functions in formulas you can perform such calculations easily by following this generic formulas syntax;
Where n is the Nth row or column to be summed in provided range
Suppose you have monthly and quarterly sales figures of salespersons and you want to sum the sales of all quarters of each salesperson. So using MOD function you can perform this calculation as per the following formula.
In this example sales figures are provided column wise and quarterly sales figure of every salesperson is available in 4th column of the selected range for each salesperson. So, you need to sum every 4th column figure in each range.
Highlight odd and even numbers
Using conditional formatting custom formula you can conditionally highlight odd and even number in a range by using MOD function.
Select the range of cells that you need to conditionally highlight. Go to Home tab > Style section> click on Conditional Formatting > New Rule > Use formula to determine which cells to format
You need to add the following two formulas in Conditional Formatting Rule Manager dialog box for even and odd numbers and select separate formatting style for each.
The formula for cells with even numbers:
Format > Fill = Green
The formula for cells with odd numbers:
Format > Fill = Gold