MOD Formula Alternative - Large Numbers

[ststern45]

Hi everyone,

I receive a "#NUM !" from the following formula:

=IF($B20="","",IF(MOD(ROUNDUP(SUM($AO20/$BX$10),0),4)=0,3,MOD(ROUNDUP(SUM($AO20/$BX$10),0),4)-1))

Cell $AO20 = 2243283778949040000
Cell $BX$10 = 262144

The #NUM ! is due to "large" numbers. I wanted to ask if there may be an alternate formula?

I will also receive the same #NUM ! error with values in the following cells:

$BX$1 = 1
$BX$2 = 4
$BX$3 = 16
$BX$4 = 64
$BX$5 = 256
$BX$6 = 1024
$BX$7 = 4096
$BX$8 = 16384
$BX$9 = 65536

I don't get the #NUM ! error with the following formula below: The value = 2
=IF($B20="","",IF(MOD(ROUNDUP(SUM($AO20/$BX$11),0),4)=0,3,MOD(ROUNDUP(SUM($AO20/$BX$11),0),4)-1))

Cell $AO20 = 2243283778949040000
Cell $BX$11 = 1048576

Thanks in advance!!

 

[Tetra201]

Try implementing the following definition of MOD:

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

 

[JoeMo]

Note also that your $AO20 cell has a 19-digit number in it which exceeds the 15-digit precision Excel provides. Excel will convert whatever digits you enter beyond the first 15 to zeroes (like the number you posted for cell $AO20).

 

[ststern45]

Ahh I forgot about the 15 digit limit. So I take it that there is no solution to this?

Thanks for all your replies.

 

[Rick Rothstein]

Ahh I forgot about the 15 digit limit. So I take it that there is no solution to this?

Here is a UDF (User Defined Function) that will handle numbers up to 28 digits. To use the function with large numbers (those that Excel wants to replace trailing numbers with zeros because of length), specify those numbers as Text (for cells, format the cell as Text or precede the number with an apostrophe). As designed, the function will return a (made up) #ArgError if the arguments are too big or zero or negative. Also note that this does not duplicate Excel's MOD function; rather, it implements VBA's Mod operator (arguments must be integer values... if you supply a floating point number, it will be rounded first before the Mod is calculated for it).

Function BigMod(Number As String, Divisor As String) As String
  If Len(Number) < 29 And Len(Divisor) < 29 And Divison > 0 Then
    BigMod = CDec(Number) - Divisor * Int(CDec(Number) / Divisor)
  Else
    BigMod = "[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ArgError]#ArgError[/URL] !"
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use BigMod just like it was a built-in Excel function. For example, if cell A1 has the number and B1 has the divisor, then use this formula...

=BigMod(A1,B1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ststern45]

Thanks Rick,

If I use a formula combin(80,20) this will give me 19 digits in which Excel will not be able to recognize? That is Excel will add bogus zeroes after the 15 position?

Is this correct??

Thanks,

Steve

 

[Rick Rothstein]

If I use a formula combin(80,20) this will give me 19 digits in which Excel will not be able to recognize? That is Excel will add bogus zeroes after the 15 position?

Is this correct??

Yes. If you want to be able to do calculations with larger numbers up to a maximum of 28 accurate digits total (that means inputted numbers and result), you would have to design your own functions to duplicate the Excel functions you need to use. The key in any such function you create is the use of the CDec function to convert numbers entered in a Variant variable to numbers that can contain up to 28 digits. In essence, that is what I did for the BigMod function.

 

[joeu2004]

The #NUM error is not due to "large" numbers per se. Instead, it is due to a defect in Excel MOD, which used to be documented in KB 119083. I cannot find that KB online anymore.

Excel MOD returns #NUM when the divisor times 2^27 is less than or equal to the numerator. That is, when the divisor is less than or equal to the numerator divided by 2^27.

So, for example, MOD(2243283778949040000, 16713766597) returns 16691175109, not #NUM .

-----

However, 2243283778949040000 is not represented exactly in Excel. Instead, its exact internal value is 2243283778949040128. Consequently, MOD(2243283778949040000, anything) is not mathematically correctly.

For example, MOD(2243283778949040000, 1048576) is actually 477056, not 477184, which 2243283778949040000 - 1048576*INT(2243283778949040000/1048576) returns.

And MOD(2243283778949040000, 16713766597) is actually 16691174981, not 16691175109, which Excel MOD returns.

 

[joeu2004]

If I use a formula combin(80,20) this will give me 19 digits in which Excel will not be able to recognize? That is Excel will add bogus zeroes after the 15 position?

Not precisely correct (no pun intended).

Excel substitutes zeros after the first 15 significant digits only when interpreting input and when formatting.

The actual internal representation might be very different.

For example, COMBIN(80,20) appears to be 3535316142212180000, but the internal value is exactly 3535316142212175360.

Nevertheless, it is unlikely that the latter is the correct number of combinations of 80 taken 20 at a time.

FYI, Excel is not limited to "15 digits of precision". It can represent any integer up 2^53 exactly; that is, up to 9007199254740992, which is a 16-digit number.