Trending October 2023 # How To Use Mod Function In Excel? # Suggested November 2023 # Top 19 Popular

You are reading the article How To Use Mod Function In Excel? updated in October 2023 on the website Vibergotobrazil.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Use Mod Function In Excel?

MOD in Excel

MOD in Excel function is a mathematical function under Math and Trigonometry category, which is used to find the remainder number after dividing a dividend by any divisor number. We all know when we divide the number 10 by 2, then we get 0 or nothing as the remainder, but when we do divide 10 by 3, then 1 is left. This is called the remainder, which we get when we use the Mod function and select both the number and divisor in the syntax.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

MOD Formula:

Below is the MOD Formula;

Where the supplied arguments are:

Number – The number to be divided.

Divisor – The number to divide by.

How to Use MOD Function in Excel?

MOD Function is very simple to use. Let us now see how to use the MOD function in Excel with the help of some examples.

You can download this MOD Function Excel Template here – MOD Function Excel Template

Example #1

The Result will be:

The MOD function returns 1 because 3 goes into 4 once with a remainder of 1.

Example #2

Get the remainder from the division

Apply the MOD Function

The Answer will be:

Now drag & drop the formula for the rest values, and the result is:

The Summary for the above Example is as follows:

Example #3

The MOD function can also be used with a vector. Now we are taking a vector of integers and the divisor 3.

A = 1:5;

M = 3;

B = MOD(A, M)

The result will be:

1 2 0 1 2

Example #4

If a vector has some negative and positive values, the result will be positive if the divisor is positive.

A = [-4 -1 7 9];

M = 3;

The result is:

2 2 1 0

Example #5

ADD only the even numbers in the given dataset:

For adding the even values in the above-given dataset, we use the below formula:

=SUMPRODUCT($A$13:$A$20*(MOD($A$13:$A$20,2)=0))

In the case of even numbers,(MOD($A$13:$A$20,2)=0) would return an array of TRUE, then the SUMPRODUCT function adds only those numbers returning TRUE.

Result is:

Example #6

ADD only the odd numbers in the given dataset:

For adding the odd values in the above-given dataset, we use the below formula:

=SUMPRODUCT($A$25:$A$31*(MOD($A$25:$A$31,2)=1))

Result is:

So, this formula works by calculating the remainder using the MOD function.

Example #7

So, this formula works by calculating the remainder using the MOD function.

Specify a number in every Nth cell. Suppose we are preparing a list of expenses for every month as per below data:

The insurance premium is paid in every 3 months gap. Here we will use the MOD function to calculate the cell in every third row with the EMI value.

The formula is:

=IF(MOD((ROW()-1),3)=0,267,””)

The result is:

Drag & drop this formula for every value, and the result is:

Here, the ROW function gives the row number, and then we subtract 1. Now the MOD function checks the remainder when this value is divided by 3. For every 3rd row, the value will be 0. In this case, IF function would return 267, else will return a blank.

Where to use the MOD function:

Where do we need to calculate ODD or EVEN numbers

OR, where do we need to find out to calculate the cell

Need to calculate the remainder

Things to Remember

The result of the MOD function has the same sign as the divisor.

If the divisor is 0, MOD returns an error value #DIV/0!

The MOD function can also be represented in terms of the INT function:

MOD(n, d)=n-d*INT(n/d)

Where n=number, d=divisor