**The Excel Function ****MOD ****Explained:**

Get Division Remainders

Get Division Remainders

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

In elementary school, you probably did long division that resulted in remainders: the parts of the numerator that didn’t fit nicely into the divisor.

It’s probably been a while since you’ve done long division, but those remainders can be quite useful. And in Excel, you can get them with the MOD function.

Let’s take a look at the MOD function as well as a few cases in which you might find it useful.

**Understanding modulo**

Before we get into the specifics of the MOD function, let’s make sure we know exactly what we’re talking about.

The MOD function is named after “**modulo**,” which is a term used in computing. The divisor in a division equation is also known as the “modulus,” giving us the name of the operation.

If a number can’t be equally divided among a divisor, there’s a “leftover”—**that’s the remainder**.

For example, **10/3 = 9 remainder 1**. Therefore, **10 mod 3 = 1**.

3 goes into 10 three times, giving us 9 . . . and there’s one left over. That’s the modulo value.

If you find this idea or the notation confusing, don’t worry—it’ll become clear when we go over some examples.

And while this information might not seem very useful, there are quite a few cases in which it’s very handy. Let’s get started and take a look.

**Using the MOD function**

The MOD function is remarkably simple. Take a look at the sparse syntax:

**The syntax of the MOD function**

**=MOD(number, divisor)**

The **number** is the number you’re dividing, and the **divisor** is the number you’re dividing it by.

So in 10/3, **10 is the number** and **3 is the divisor**.

That’s really all there is to it.

Open up the example workbook and we’ll take a look at a case in which this information might be useful.

On the first sheet, we have a number of classes. Each class has an associated group size, but most of them have more than class members than fit into a single group.

If each class is split into groups, there may be a few people left over. The MOD function tells us how many.

Click into cell C2 and type the following formula:

**=MOD(A2, B2)**

In this case, A2 is the number, and B2 is the divisor (we’re dividing A2 by B2).

Hit **Enter** and let’s see what happens.

The function returns a 1. A class of 46 can be divided into three equal groups of 15—but there will be one left over.

Click and drag the function down to fill in the rest of the cells in column C.

All the results should be what you expect.

**Except for the last one.**

Here’s where the MOD function behaves a little strangely. Because 19 doesn’t fit into 20 evenly, Excel returns the entire number as the modulo value.

**For this reason, you should always double-check your MOD results.**

**Combining MOD with other functions**

This is where MOD gets **really cool**.

You can combine MOD with other functions to solve all sorts of problems. One of the most common is to perform some operation on **every nth row or column**.

This example gets a little complicated, so we’re going to break down each step. Don’t be intimidated by the whole formula!

On the second sheet of the workbook, we have several sales numbers for each month.

We can combine MOD with SUM and COLUMN to get the total sales for odd months, even months, every third month, and so on.

Here’s the formula we’re going to use to get the **sum of sales for odd months**:

**=SUM(B2:G4*(MOD(COLUMN(B2:G2), 2)=0))**

Let’s start in the middle, with the COLUMN function.

COLUMN returns the column number for all the cells in the range. So for B2, it returns 2. For C2, 3. For D2, 4. And so on.

MOD, as we’ve seen, returns the remainder of a division equation. The number divided in this case is the column number of each cell, and the divisor is 2.

In essence, this returns a 0 for **every even column** (keep in mind that we’re using the even columns because January is in column 2).

The **=0** is important here. This creates a logical statement. Whenever the MOD function returns a 0, we’ll have 0=0, which resolves to TRUE. Excel treats TRUE as 1.

When MOD returns 1, we’ll have 1=0, which is FALSE. FALSE is also treated as a 0.

The sum of each column is multiplied by that 1 or 0, and then added to the final total.

This formula is a rather complicated one. But it’s a great example of how you can use MOD to solve some rather difficult problems.

This is an array function, so to properly execute it, you’ll need to press **Ctrl + Shift + Enter**.

After that, take a deep breath—you have the sum of all the sales in odd months!

Feel free to sum each column individually and add the results to check the math. It works!

If you want to sum every third or fourth month, just change the divisor from 2 to 3 or 4.

**Find remainders with ease**

The MOD function is simple, and it’s easy to overlook.

But when you start combining it with other functions, it gets really powerful. Don’t underestimate how useful it can be!

And when you need a simple remainder calculated, you know what to use.