Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)™. Register today!

MOD Function

Returns the remainder after a number (dividend) is divided by another number (divisor)

What is the MOD Function?

The MOD function is categorized under Math and Trigonometry functions. The function helps find a remainder after a number (dividend) is divided by another number (divisor).

As a financial analyst, the MOD function is useful when we need to process every nth value. For example, if our data comprises several columns of numbers, we can use MOD to sum every nth column.

 

Formula

=MOD(number,divisor)

 

The MOD Function uses the following arguments:

  1. Number (required argument) – It is the number for which we wish to find the remainder.
  2. Divisor (required argument) – It is the number by which we want to divide the number.

 

How to use the MOD Function in Excel?

To understand the uses of the MOD function, let’s consider a few examples:

 

Example 1

Suppose we are given the following data:

 

MOD Function

 

In the above data, we used the following formula:

 

MOD Function - Example 1

 

The results we get in Excel are shown below:

 

MOD Function - Example 1a

 

Example 2

Suppose we are given the following data:

 

MOD Function - Example 2

 

If we wish to get the maximum value of every nth column, we can use an array formula that is based on the MAX, MOD and COLUMN functions.

The formula to use is:

 

MOD Function - Example 2a

 

As it is an array formula, we need to enter it using Ctrl + Shift + Enter. In the formula above, we used the MOD and COLUMN functions to filter out values not in the nth columns, then runs MAX on the result.

We get the result below:

 

MOD Function - Example 2b

 

Suppose we need to find the MIN function, the formula will be {=MIN(IF(MOD(COLUMN(B5:H5)-COLUMN(B5)+1,I5)=0,B5:H5))}.

Further, if we wish to get the SUMPRODUCT of the even rows, we can use the following formula:

 

MOD Function - Example 2c

 

We get the result below:

 

MOD Function - Example 2d

 

For odd rows, the formula will be:

 

MOD Function - Example 2e

 

We get the result below:

 

MOD Function - Example 2f

 

Few things to remember about the MOD Function 

  1. #DIV/0! error – Occurs when the divisor value is zero.
  2. The MOD function will return a result in the same sign as the divisor.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!