EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Functions Excel MOD Function
Secondary Sidebar
Excel Functions
  • Excel Functions
    • Excel Column Total
    • VLOOKUP to Return Multiple Values
    • User Defined Function in Excel
    • Excel SWITCH Function
    • Excel FV Formula
    • Excel AVERAGEIF
    • Simple Formula in Excel
    • Excel Scatter Chart
    • Excel Bar Chart
    • LINEST Excel Function
    • RANK Excel Function
    • Excel MOD Function
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Certification Course
  • EXCEL Certification COURSE

Excel MOD Function

By Madhuri ThakurMadhuri Thakur

MOD in Excel

MOD Function (Table of Contents)

  • MOD in Excel
  • MOD Formula in Excel
  • How to Use MOD Function in Excel?

MOD in Excel

Mod function is 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 number 10 with 2 then we get 0 or nothing as remainder but when we do divide 10 by 3 then 1 is left this is called the remainder and this we get when we use Mod function and selecting both number and divisor in the syntax.

MOD Formula:

Below is the  MOD Formula;

Start Your Free Excel Course

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

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.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)
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 above Example is as follows:

MOD Example 2-5

Example #3

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, then 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 case of even numbers,(MOD($A$13:$A$20,2)=0) would return an array of TRUE, then SUMPRODUCT function adds only those numbers that are 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 SUMPRODUCT function adds only those numbers that are returning TRUE.

Result is:

MOD Example 6-3
So, this formula is working 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 of every month as below data:

 MOD Example 7-1

The insurance premium is paid in every 3 months gap. Here we will use 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 Example 7-2

The Result is:

MOD Example 7-3

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

MOD 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 MOD function:

  • Where we need to calculate ODD or EVEN numbers
  • Where we need to find out to calculate the cell
  • Need to calculate the remainder

Things to Remember

  • The result of MOD function has the same sign as the divisor.
  • If divisor is 0, MOD returns an error value #DIV/0!
  • The MOD function can also be represented in terms of INT function as:

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

Where n=number, d=divisor

Recommended Articles

This has been a guide to Excel MOD function. Here we discuss the MOD Formula and how to use MOD function along with practical examples and 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
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download MOD Function Excel Template

EDUCBA

Download MOD Function Excel Template

EDUCBA

डाउनलोड MOD Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more