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.

MOD function in Excel

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

2. The MOD function below returns 1.

Modulo Operation

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

3. The MOD function below returns 0.

Remainder

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.

Even

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

Odd

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

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

MOD and 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.

Sum Every nth Row in Excel

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Using 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.