Mod

The MOD function in Excel gives the remainder of a division. As always, we will use easy examples to make things more clear.

1. The MOD function below returns 9.

Explanation: 59 divided by 10 equals 5 with a remainder of 9.

2. The MOD function below returns 1.

Explanation: 7 divided by 2 equals 3 with a remainder of 1.

3. The MOD function below returns 0.

Explanation: 36 divided by 6 equals 6 with a remainder of 0.

Use IF and MOD to determine if a number is even or odd. Even numbers divided by 2 always give a remainder of 0. Odd numbers divided by 2 always give a remainder of 1.

4. For example, 28 divided by 2 equals 14 with a remainder of 0. As a result, the formula below returns Even.

5. For example, 29 divided by 2 equals 14 with a remainder of 1. As a result, the formula below returns Odd.

Let's take a look at one more cool example.

6. The formula below returns 0 for every 3rd row.

Explanation: the ROW function returns the row number of a cell. For the first row, MOD(1,3) = 1 because 1 divided by 3 equals 0 with a remainder 1. For the third row, MOD(3,3) = 0 because 3 divided by 3 equals 1 with a remainder of 0.

7. Use this formula to sum every 3rd row in Excel.

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Visit our page about Summing Every nth Row for detailed instructions on how to create this array formula.