Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL: MOD() Function Returns #NUM! Error Value

XL: MOD() Function Returns #NUM! Error Value

View products that this article applies to.

Symptoms

The MOD() function in Microsoft Excel may return a #NUM! error value instead of a valid remainder (modulus).

↑ Back to the top

Cause

The MOD() function returns the #NUM! error if the following condition is true:
('divisor' * 134217728) is less than or equal to 'number'

↑ Back to the top

Workaround

If the above condition is true, and the MOD() function returns a #NUM! error, you can use the following formula to calculate the remainder (modulus) of two numbers:
=number-(INT(number/divisor)*divisor)
For example, instead of using this formula
=MOD(J13,K13)
use this formula:
=J13-(INT(J13/K13)*K13)

↑ Back to the top

More information

In Microsoft Excel, the MOD() function returns the remainder (modulus) of dividing one number by another. The MOD() function accepts two arguments: 
   Argument   Definition
   --------------------------------------
   Number     The number being divided
   Divisor    The number being divided by
If you want to calculate the remainder (modulus) of 22 divided by 5, you would use the following formula:
=MOD(22,5)
The result is 2 (22 divided by 5 leaves a remainder of 2).

If the divisor argument, multiplied by 134,217,728 (or 2 raised to the 27th power), is less than or equal to the number argument, the MOD() function returns a #NUM! error value.

↑ Back to the top

Keywords: KB119083, kbprb

↑ Back to the top

Article Info
Article ID : 119083
Revision : 3
Created on : 1/19/2007
Published on : 1/19/2007
Exists online : False
Views : 552