EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Functions Excel MOD Function
 

Excel MOD Function

Madhuri Thakur
Article byMadhuri Thakur

Updated August 9, 2023

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

MOD Formula

Below is the  MOD Formula;

MOD Formula in Excel

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

Let’s consider n=4, d=3. Apply the MOD Function

MOD Example 1-1

The Result will be:

MOD Example 1-2

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

Example #2

Get the remainder from the division

MOD Example 2-1

Apply the MOD Function

MOD Example 2-2

The Answer will be:

MOD Example 2-3

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

MOD Example 2-4

The Summary for the above Example is as follows:

MOD Example 2-5

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;

B = MOD(A, M)

The result is:

2     2     1      0

Example #5

ADD only the even numbers in the given dataset:

MOD Example 5-1

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))

MOD Example 5-2

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:

MOD Example 5-3

Example #6

ADD only the odd numbers in the given dataset:

MOD Example 6-1 

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))

MOD Example 6-2

In case of odd numbers, (MOD($A$25:$A$31,2)=1)would return an array of TRUE, then the SUMPRODUCT function adds only those numbers returning TRUE.

Result is:

MOD Example 6-3
So, this formula works by calculating the remainder using the MOD function.

Example #7

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

 mod in excel Example 7-1

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,””)

 mod in excel Example 7-2

The result is:

mod in excel Example 7-3

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

mod in excel Example 7-4

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

Recommended Articles

This has been a guide to the Excel MOD function. Here we discuss the MOD Formula and how to use the MOD function, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

  1. SUBSTITUTE Function in Excel
  2. RIGHT Excel Function
  3. LEN Function in Excel
  4. MODE Excel Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download MOD Function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download MOD Function Excel Template

EDUCBA

डाउनलोड MOD Function Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW