MOD Function In Excel

This Excel Tutorial demonstrates how to use the Excel MOD Function to calculate the remainder after dividing.

MOD MAIN

MOD Function Overview

The MOD function returns the remainder, or modulus, of a number after performing division. However, the MOD function isn’t strictly for helping us with our division problems. It becomes even more powerful for when we want to look for every Nth item in a list, or when we need to generate a repeating pattern.

To use the MOD Excel Worksheet Function, Select cell and type:

mod formula syntax

MOD Function Syntax and Inputs:

number – A number.

divisor – A number to divide by.

What is the MOD function?

The MOD function returns the remainder, or modulus, of a number after performing division. However, the MOD function isn’t strictly for helping us with our division problems. It becomes even more powerful for when we want to look for every Nth item in a list, or when we need to generate a repeating pattern.

MOD Basic Math

When you try to divide 13 by 4, you can say the answer is 3 remainder 1. The “1” in this case is specifically known as the modulus (hence the MOD Function name). In a formula then, we could write

13 MOD 4

And the output would be 1.

Looking at this table gives some more illustrations of how the input/output of MOD will work.

Basic Math

Note that when the input was 3, there was no remainder and thus the output of the formula was 0. Also, in our table we used the ROW function to generate our values. Much of the power of MOD will be from using the ROW (or COLUMN) function as we’ll see in the following examples.

MOD Sum every other row

Consider this table:

Every other row Table

For illustration purposes, the second column has the formula

Every other rowTo add all the even rows, you could write a SUMIF formula and have the criteria look for 0 values in col B. Or, to add all the odd rows, have the criteria be to look for 1 values.

However, we don’t need to create the helper column at all. You can combine the power of MOD within SUMPRODUCT to do it all in one step. Our formula for this would be

Since it’s within SUMPRODUCT, the MOD function will be able to handle our array input. We’ve seen the output already in the helper column, but the array from our MOD in this formula will be {0, 1, 0, 1}. After checking for values that equal 0 applying the double unary, the array will be {1, 0, 1, 0}. The SUMPRODUCT then does it’s magic or multiplying the arrays to produce {2, 0, 4, 0} and then summing to get desired output of 6.

MOD Sum every Nth row

Since a formula of MOD(x, N) will output a 0 at every Nth value, we can use this to help formulas pick and choose which values to use in other functions. Look at this table.

Every Nth ROW Table

Our goal is to grab the values from each row marked “Total”. Take note that the Total appears every 3rd row, but starting at row 4. Our MOD function will thus use 3 as the 2nd argument, and we need to subtract 1 from the first argument (since 4 -1 = 3). This way the desired rows we want (4, 7, 10) will be multiples of 3 (3, 6, 9). Our formula to sum the desired values will be

Every Nth ROWThe array produced will transform like this:

Our SUMPRODUCT’s criteria array is now setup how we need to grab every 3rd value, and we’ll get our desired result of $90.

MOD Sum on Columns

We’ve been using examples so far that go vertically and use ROW, but you can also go horizontally with the COLUMN function. Consider this layout:

MOD Column Table

We want to sum up all the items. Our formula for this could be

In this case, we’re setup to grab every 2nd column within our range, so the SUMPRODUCT will only retain non-zero values for columns B & D. For reference’s here’s a table showing column numbers and their corresponding value after taking MOD 2.

Mod SUM Column

Highlight every Nth row

Another common place to use the MOD function is when you want to have a highlighted row appear every Nth row. The general form for this will be

Where N is the number of rows between each highlighted row (I.e., to highlight every 3rd row, N = 3), and Offset is optionally the number we need to add or subtract to get our first highlighted row to align with N (I.e., to highlight every 3rd row but start at row 5, we’d need to subtract 2 since 5 -2 = 3). Note that with the ROW function, by omitting any arguments, it will return the row number from cell the formula is in.

Let’s use our table from before:

Highlight Nth Row

To apply a highlight to all the Total rows, we’ll create a new Conditional Formatting rule with a formula of

When the conditional formatting applies this formula, row 2 will see

Row 3 will experience a similar output, but then row 4 will see

We thus have our rule working correctly, as shown here:

Highlight Nth Row

Highlight Integers or Even numbers

Rather than highlighting specific rows, you could also check the actual values within the cells. This could be useful for when you want to find numbers that are multiples of N. For instance, to find multiples of 3 your conditional format formula would be

Up to this point, we’ve been dealing with whole numbers. You can have an input of a decimal (e.g. 1.234) however and then divide by 1 to get only the decimal portion (e.g. 0.234). This formula looks like

Knowing that, to highlight only integers the conditional format formula would be

Concatenate every N Cells

We’ve previously been using MOD to tell the computer when to grab at value at every Nth item. You could also have it be used to trigger a larger formula to be executed. Consider this layout:

concatenate every n cells Table

We want to concatenate the names together, but only on every 3rd row starting on row 2. The formula used for this is

concatenate every n cellsOur MOD function is what’s acting as the criteria for the overall IF function. In this example, we needed to add 1 to our ROW, because we’re starting at row 2 (2 + 1 = 3). When the MOD’s output is 0, the formula does the concatenation. Otherwise, it just returns blank.

Count even/odd values

If you’ve ever needed to count how many even or odd values are in a range, you’ll know that COUNTIF doesn’t have the ability to do this. We can do it with MOD and SUMPRODUCT though. Let’s look at this table:

Count odd cells Table

The formula we’ll use to find the odd values will be

Count odd cellsRather than loading in some row numbers, our MOD is going to load in the actual cells’ values into the array. The overall transformation will then progress like so:

The SUMPRODUCT then adds up the values in our array, giving desired answer of: 5.

Repeating pattern

All the previous examples have been checking the output of MOD for a value. You can also use MOD to generate a repeating pattern of numbers, which can in turn be very helpful.

First, let’s say that we had a list of items we want repeated.

Repeat Table

You could try and manually copy & paste however many times you need, but that would get tedious. Instead, we’ll want to use the INDEX function to retrieve our values. For the INDEX to work, we need the row argument to be a sequence of numbers that goes {1, 2, 3, 1, 2, 3, 1, etc.}. We can accomplish this using MOD.

First, we’ll start with just the ROW function. If you start with

And then copy this downwards, you get the basic number sequence of {1, 2, 3, 4, 5, 6, …}. If we applied the MOD function with 3 as the divisor,

we’d get {1, 2, 0, 1, 2, 0, …}. We see that we’ve got a repeating pattern of “0, 1, 2”, but the first series is missing the initial 0. To fix this, back up a step and subtract 1 from the row number. This will change our starting sequence to {0, 1, 2, 3, 4, 5, …}

And after it comes out of the MOD, we have {0, 1, 2, 0, 1, 2, …}. This is getting close to what we need. The last step is to add 1 to the array.

Which now produces a number sequence of {1, 2, 3, 1, 2, 3, …}. This is our desired sequence! Plugging it into an INDEX function, we get our formula of

The output will now look like this:

Repeating Pattern

 

MOD Examples in VBA

You can also use the LINEST function in VBA.

Within VBA, MOD is an operator (just like plus,minus, multiplication and division operators). So, executing the following VBA statements

will produce the following results

Vba MOD function

 

For the function arguments (known_y’s, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel

Google Sheets MOD Function

The MOD Function works exactly the same in Google Sheets as in Excel:

MOD Google