Function MOD (Modulo) of Excel

Function MOD (Modulo) of Excel
Last Updated on 12/05/2024
Reading time: 3 minutes

The MOD function of Excel returns the rest of a division. This function is very useful for

Presentation of the MOD function of Excel

The MOD function needs two argument

  1. Number

    The number for which you want to find the remainder.

  2. Divisor

    The number by which you divide

=MOD(Number, Divisor)

MOD will return the rest of the division. Is that clear? Let's see when to use it with the following example: You are the manager of an egg company. Every day, you receive eggs, which you pack in egg packs of 6.

Put eggs in egg packs

So you have 2 important questions to solve

  1. How many boxes will I fill?
  2. How many leftover eggs will you have in the last box?

Imagine receiving 8565 eggs (the number), and you will pack them by 6 (the divisor). Answering the first question is very easy. You must divide and keep the integer part (at the end of the article you will see another solution)

=INT(8565/6)

Number of egg boxes filled

It's for answering the second question that the MOD function is very useful.

=MOD(8565,6)

The MOD function of Excel returns the number of leftover eggs

Now, if you use a box with 12 spaces, your Excel dashboard will automatically recalculate your model

New result for 12 spaces

MOD function in a logical test

You can also use the MOD function in a logical test, like, for instance, the odd or even numbers.

  • Even numbers are 0, 2, 4, 6, 8, 10, .....
  • Odd numbers are 1, 3, 5, 7, 9, 11, .....

There is always a step of 2 units between each number. So, a division by 2 will return 0 or 1

The division by 2 returns always 0 or 1

And we can convert the formula to a logical test like this

  • =MOD(A2,2)=0 for the Even Number
  • =MOD(A2,2)<>0 for the Odd Number
Convert the formula into a logical test

Now, you can use your test, either with the IF function, or in a conditional formating rules.

For instance, in this example, we have used exactly the same test as cutom rules and only the Even numbers are colored

Highlight the even numbers in the cells

QUOTIENT function

For the very first example, we have calculate the number of boxes with a division inside the INT function.

But we could also returned the same result with the QUOTIENT function

=QUOTIENT(B1,B2) = INT(B1/B2) =>1427

QUOTIENT function in Excel

1 Comment

  1. Brad R
    17/03/2018 @ 06:36

    Hi ,Could you please advise if it is possible to reference a date with a value to be repeated by frequency,( ie 7 days, 30, 90 days,180 days) from that date using Mod & IF syntax nested
    Thanks
    Brad

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Function MOD (Modulo) of Excel

Reading time: 3 minutes
Last Updated on 12/05/2024

The MOD function of Excel returns the rest of a division. This function is very useful for

Presentation of the MOD function of Excel

The MOD function needs two argument

  1. Number

    The number for which you want to find the remainder.

  2. Divisor

    The number by which you divide

=MOD(Number, Divisor)

MOD will return the rest of the division. Is that clear? Let's see when to use it with the following example: You are the manager of an egg company. Every day, you receive eggs, which you pack in egg packs of 6.

Put eggs in egg packs

So you have 2 important questions to solve

  1. How many boxes will I fill?
  2. How many leftover eggs will you have in the last box?

Imagine receiving 8565 eggs (the number), and you will pack them by 6 (the divisor). Answering the first question is very easy. You must divide and keep the integer part (at the end of the article you will see another solution)

=INT(8565/6)

Number of egg boxes filled

It's for answering the second question that the MOD function is very useful.

=MOD(8565,6)

The MOD function of Excel returns the number of leftover eggs

Now, if you use a box with 12 spaces, your Excel dashboard will automatically recalculate your model

New result for 12 spaces

MOD function in a logical test

You can also use the MOD function in a logical test, like, for instance, the odd or even numbers.

  • Even numbers are 0, 2, 4, 6, 8, 10, .....
  • Odd numbers are 1, 3, 5, 7, 9, 11, .....

There is always a step of 2 units between each number. So, a division by 2 will return 0 or 1

The division by 2 returns always 0 or 1

And we can convert the formula to a logical test like this

  • =MOD(A2,2)=0 for the Even Number
  • =MOD(A2,2)<>0 for the Odd Number
Convert the formula into a logical test

Now, you can use your test, either with the IF function, or in a conditional formating rules.

For instance, in this example, we have used exactly the same test as cutom rules and only the Even numbers are colored

Highlight the even numbers in the cells

QUOTIENT function

For the very first example, we have calculate the number of boxes with a division inside the INT function.

But we could also returned the same result with the QUOTIENT function

=QUOTIENT(B1,B2) = INT(B1/B2) =>1427

QUOTIENT function in Excel

1 Comment

  1. Brad R
    17/03/2018 @ 06:36

    Hi ,Could you please advise if it is possible to reference a date with a value to be repeated by frequency,( ie 7 days, 30, 90 days,180 days) from that date using Mod & IF syntax nested
    Thanks
    Brad

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *