How to Use Excel MOD Function (Examples + Video)

Excel MOD Function (Example + Video)

Excel MOD Function

When to use Excel MOD Function

MOD function can be used when you want to get the remainder when one number is divided by another number.

What it Returns

It returns a numerical value that represents the remainder when one number is divided by another.

Syntax

=MOD(number, divisor)

Input Arguments

  • number – A numeric value for which you want to find the remainder.
  • divisor – A number with which you want to divide the number argument. If the divisor is 0, then it will return the #DIV/0! error.

Additional Notes

  • If the divisor is 0, then it will return the #DIV/0! error.
  • The result always has the same sign as that of the divisor.
  • You can use decimal numbers in the MOD function. For example, if you use the function =MOD(10.5,2.5), it will return 0.5.

Examples of Using MOD Function in Excel

Here are useful examples of how you can use the MOD function in Excel.

Example 1 – Add Only the Odd or the Even Numbers

Suppose you have a dataset as shown below:

Excel MOD Function - Dataset

Here is the formula you can use to add only the even numbers:

=SUMPRODUCT($A$2:$A$11*(MOD($A$2:$A$11,2)=0))

Note that here I have hard coded the range ($A$2:$A$11). If your data is likely to change, you can use an Excel Table or created a dynamic named range.

Similarly, if you want to add only the odd numbers, use the below formula:

=SUMPRODUCT($A$2:$A$11*(MOD($A$2:$A$11,2)=1))

MOD function to calcuate odd numbers in Excel

This formula works by calculating the remainder using the MOD function.

For example, when calculating the sum of odd numbers, (MOD($A$2:$A$11,2)=1) would return an array of TRUEs and FALSEs. If the number is odd, it would return a TRUE, else a FALSE.

SUMPRODUCT function then only adds those numbers that are returning a TRUE (i.e., odd numbers).

The same concept works when calculating the sum of even numbers.

Example 2 – Specify a Number in Every Nth Cell

Suppose you are making a list of fix expenses every month as shown below:

Using MOD Function to specify an amount every three months

While the house and car EMIs are monthly, the insurance premium is paid every three months.

You can use the MOD function to quickly fill the cell in every third row with the EMI value.

Here is the formula that will do this:

=IF(MOD((ROW()-1),3)=0,457,””)

Fill every nth cell with a value in Excel

This formula simply analyzes the number given by ROW()-1.

ROW function gives us the row number and we have subtracted 1 as our data starts from second row onwards. Now the MOD function checks the remainder when this value is divided by 3.

It will be 0 for every third row. Whenever this is the case, IF function would return 457, else it will return a blank.

Example 3 – Highlight Alternate Rows in Excel

Highlighting alternate rows can increase the readability of your data set (especially when it’s printed).

Something as shown below:

Highlight Every other row using MOD Function

Note that every second row of the dataset is highlighted.

While you can do this manually for a small dataset, if you have a huge one, you can leverage the power of conditional formatting with the MOD function.

Here are the steps that will highlight every second row in the dataset:

  • Select the entire data set.
  • Go to the Home tab.
  • Click on Conditional Formatting and select New Rule.conditional formatting in incell charts
  • In the ‘New Formatting Rule’ dialog box, select ‘Use a formula to determine which cells to format’.Conditional Formatting Use Formula
  • In the formula field, enter the following formula: =MOD(ROW()-1,2)=0Mod function in Conditional Formatting dialog box
  • Click on the Format button and specify the color in which you want the rows highlighted.
  • Click OK.

This will instantly highlight alternate rows in the dataset.

You can read more about this technique and variations of it in this tutorial.

Example 4 – Highlight All the Integer /Decimal Values

You can use the same conditional formatting concept shown above to highlight integer values or decimal values in a data set.

For example, suppose you have a dataset as shown below:

Highlight Numbers with decimals

There are numbers that are integers and some of these have decimal values as well.

Here are the steps to highlight all the numbers that have decimal value in it:

  • Select the entire data set.
  • Go to the Home tab.
  • Click on Conditional Formatting and select New Rule.conditional formatting in incell charts
  • In the ‘New Formatting Rule’ dialog box, select ‘Use a formula to determine which cells to format’.Conditional Formatting Use Formula
  • In the formula field, enter the following formula: =MOD(A1,1)<>0
  • Click on the Format button and specify the color in which you want the rows highlighted.
  • Click OK.

This will highlight all the numbers that have a decimal part to it.

Data with decimal part highlighted

Note that we in this example, we have used the formula =MOD(A1,1)<>0. In this formula, make sure the cell reference is absolute (i.e., A1) and not relative (i.e., it shouldn’t be $A$1, $A1 or A$1).

Excel MOD Function – Video Tutorial

Related Excel Functions: