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

What Is MOD Function in Excel?

The 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:

Arguments Explanation:

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.

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

MOD Function Not Working in Excel: 3 Reasons with Solutions

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.

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.

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.

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

• So, change the divisor of the C8 cell and you will get the remainder.

Read More: How to Get Remainder in Decimal in Excel

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.

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

• Secondly, press ENTER to get the result.

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

Read More: How to Divide Without Remainder in Excel

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.

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.

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

Download Practice Workbook

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

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. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Separate Quotient And Remainder in Excel
  
• Excel VBA: How to Divide Without Remainder