Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

[Fixed!] Excel MOD Function Not Working (3 Issues with Solutions)

Sometimes you may need to use the MOD function in Excel. Eventually, the MOD function may not be working in Excel. In that case, if you are looking for the reasons along with solutions why this MOD function is not working in Excel, then you have come to the right place. Today, in this article, I will explain the reasons along with solutions why this MOD function is not working in Excel.

Furthermore, for conducting the session, I will use Microsoft 365 version.


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


What Is MOD Function in Excel?

MOD function is a built-in function in Excel. Basically, this MOD function gives the remainder after a specified division. Actually, the MOD function has two arguments. One is a number or dividend, the other is the divisor. For your better understanding, I’m giving an introduction to the MOD function.

Function Objective:

Returns the remainder after a number is divided by a divisor.

Syntax:

=MOD(number, divisor)

What Is MOD Function in Excel

Arguments Explanation:

Argument Required/Optional Explanation
number Required Dividend or the number that has to be divided.
divisor Required Integer number, by which you want to divide the dividend.

Return Parameter:

The remainder of a division.

In the following example, B5 is the dividend and C5 is the divisor. However, this is a simple example. So, when you divide 3 by 2, the quotient will be 1. And the remainder will be 1.

Use of Excel MOD Function to Show Why This is Not Working

So, after pressing ENTER, you will see 1 as the result.

Read More: How to Separate Quotient And Remainder in Excel (5 Easy Ways)


3 Reasons with Solutions for MOD Function Not Working in Excel

Here, I will describe 3 suitable solutions for why this MOD function is not working in Excel. In addition, for your better understanding, I’m going to use a sample dataset. Where you will see some numbers and divisors.


Reason 1: Get a Warning from Microsoft Excel

Now, see the following issue. Where, I was going to use the MOD function, but after using a comma I couldn’t insert the divisor value.

Reasons for MOD Function Not Working in Excel

At this time, Microsoft Excel gives the following warning.

Now, let’s see the solution to this issue. Actually, the main reason for this issue is the list separator may turn into a tab from a comma.


Solution: Change Customize Format

  • Firstly, from the Search box of your device >> go to the Control Panel window.

After opening the Control Panel window follow the 2nd step.

  • Secondly, from the Category option >> choose Large icons.

Solutions for MOD Function Not Working in Excel

As a result, another window named All Control Panel Items will appear.

  • Then, go to the Region item.

Subsequently, a new dialog box named Region will pop up.

  • After that, from that dialog box choose Additional settings which are under the Formats.

At this time, another dialog box named Customize Format will pop up.

  • Consequently, from that dialog box change the List separator to comma >> then press OK.

Change Customize Format as Solution for Excel MOD Function Not Working

  • Similarly, press OK on the previous dialog box named Region.

  • Lastly, go back to your Excel sheet.
  • Now, you can easily use the MOD function.

  • Now take your mouse cursor to the bottom right corner of cell D6. Here, the cursor shows the cross sign (+), which is called Fill Handle.
  • Then, double-click on this Fill Handle icon to apply the same formula to the rest of the cells.

  • As a result, you will find out all the remainder.
  • Here, if you notice then you will see a #DIV/0! error. As the divisor for this was 0.

Read More: How to Get Remainder in Decimal in Excel (3 Suitable Ways)


Reason 2: Limitation of MOD Function for Digits of Number

There may be another issue with the MOD function not working in Excel. Actually, when you use a large number then sometimes there may not the MOD function work. In that case, you can use a modified version of the MOD function.


Solution: Apply Modified MOD Function

  • Firstly, write down the following formula in the D5 cell.
=MOD(MOD(B5,134217728*C5),C5)

Here, in this formula, B5 is the dividend and C5 is the divisor.

  • Secondly, press ENTER to get the result.

Apply Modified MOD Function for Not Working in Excel

  • Then, use the same formula for others and you will find all the remainder.

Read More: How to Divide Without Remainder in Excel (3 Effective Methods)


Reason 3: Use Non-Integer Numbers

Now, I will talk about another issue with the MOD function not working in Excel. Which are about floating points. Actually, most of the non-integer terms may not represent exactly what they are. On the other hand, MOD function counts the divisor as an integer number. Thus, you may use the MOD function along with the ROUND function to solve this problem.


Solution: Merge Excel ROUND and MOD Functions

  • Firstly, write down the following formula in the D5 cell.
=MOD(ROUND(B5,2),C5)

Here, in this formula, B5 is the dividend and C5 is the divisor. The ROUND function will take the decimal part up to 2 places.

  • Secondly, press ENTER to get the result.

Merge Excel ROUND and MOD Functions as MOD Function Not Working

  • Similarly, use the same formula for others and you will find all the remainder.


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to Use MOD function in Excel


Conclusion

I hope you found this article helpful. Here, I have explained 3 possible solutions for why the MOD function is not working in Excel. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo