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 {}. In Excel 365 or Excel 2021, finish by simply pressing Enter. You won't see curly braces. Visit our page about Summing Every nth Row for instructions on how to create this array formula.