Calculate MOD of Large Numbers in Excel

.

Excel Trick to Calculate MOD of Big Numbers.

This article is all about MOD function in Excel. Every aspect of the MOD function is covered here in this article. At the end of this article, I have also explained a VBA code to calculate the MOD of large numbers.
I have also explained the shortcomings of the MOD formula in Excel and how to overcome those shortcomings.

Let’s start with the basics – What is the MOD function?

What is MOD Function in Excel

MOD is a short form of Modulus. Modulus is a function that returns the remainder when you divide a number with another number.
For Example: 19 MOD 5 = 4 ( Explanation : If you divide 19 with 5 then remainder will be 4 (5*3 = 15 and 19-15 = 4)

How to calculate MOD using Excel Formula

Using simple Excel Formula, you can easily calculate the MOD of any 2 numbers.

MOD Formula in Excel

MOD Function

As shown in the above picture, you can see there are two input parameters required for this Function. Number and Divisor.

Important Note:

Using the above formula, you can calculate MOD of a maximum of 12 digits. If number goes beyond 12 digits, then you get #NUM! error as shown below:

Mod Error for Big Numbers

Mod Error for Big Numbers

Need of Calculating MOD of Big Numbers

Yesterday, In office, one of my colleague (Bas Vermeulen) was trying to calculate the IBAN Numbers for a Given BANK Name, Country Code and Bank Account Number. In the process of calculating IBAN Number, he needed to calculate MOD-97 of really bog number (approx 22 digit number).

Therefore, to help him, I created a VBA function that can calculate the MOD of any number of digits

VBA Code to Calculate MOD of Big Numbers

To overcome the MOD function error, I came up with the following logic, so that no matter how big the number is, MOD can still be calculated. Therefore, I went back to the basics of Division, which I had studied in my school days – How to divide a big number.

So here is what you do.

Step 1: First pick up the first n length of digit from the Large Number provided (Where n = Length of Divisor + 1) – Just to make sure that the big number is divided by a small number.
Step 2: Now for this subset of the digit, it is easy to calculate the MOD and get the Remainder
Step 3: Now, simply append this remainder in the remaining digit of the larger number
Step 4: Now using this as a new Number repeat the steps from Step 1…

Refer to the image below for more clarity

Find MOD Manually - Divide Method

Find MOD Manually – Divide Method

With the above process, it is clear that, if we automate the above step in VBA then, there you can calculate the MOD of any big number. Because at a time, you are dividing a subset of the whole number. The number of iterations will increase based on how big the number is – but it will be able to calculate.


Function MODForBigNum(bigNum As String, modVal As Integer) As Integer
   
    Dim currentLenNum As Integer
    Dim partitionCount As Integer
    Dim remainingString As String
    
    currentLenNum = VBA.Len(bigNum)
    partitionCount = VBA.Len(modVal) + 1
    
    remainingString = bigNum
   
    While currentLenNum > partitionCount
        stringToInt = VBA.Left(remainingString, partitionCount)
        remainingString = VBA.Right(remainingString, currentLenNum - partitionCount)
        remainder = stringToInt - (Int(stringToInt / modVal) * modVal)
        remainingString = remainder & remainingString
        currentLenNum = VBA.Len(remainingString)
    Wend
    
    MODForBigNum = remainingString - (Int(remainingString / modVal) * modVal)
    
End Function

There is one shortcoming in the above code – in case when Divisor is more than 5 digits – it will give an error
To overcome the above problem, I did some changes to the above code and now it should be working any number of Number and Divisor

The only, issue with the below function is that it accepts Divisor as a string and also returns the result as a string.


Function MODForBigNum2(bigNum As String, modVal As String) As String
   
    Dim currentLenNum As Integer
    Dim partitionCount As Integer
    Dim remainingString As String
    
    currentLenNum = VBA.Len(bigNum)
    partitionCount = VBA.Len(modVal) + 1
    
    remainingString = bigNum
   
    While currentLenNum > partitionCount
        stringToInt = VBA.Left(remainingString, partitionCount)
        remainingString = VBA.Right(remainingString, currentLenNum - partitionCount)
        remainder = stringToInt - (Int(stringToInt / modVal) * modVal)
        remainingString = remainder & remainingString
        currentLenNum = VBA.Len(remainingString)
    Wend
    
    MODForBigNum2 = remainingString - (Int(remainingString / modVal) * modVal)
    
End Function

Calculate Mod-97 of Large Numbers in Excel

So, now you have a solution to calculate Mod for a big Number and Big Divisor.
In my next article, I will Publish a Tool to calculate IBAN Numbers automatically.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

3 Comments

  1. Jaime

    Muy buena explicación y genial para ejercitarse en macros

    Reply
  2. ALP

    It fails on few big numbers I tried

    Reply
  3. A.

    Hi, Thank you very much for what you do.
    I am trying your Script and is good until it reaches 2^40 but crashes at 2^50.
    I was actually looking for a number like 256^77 Mod 220 and I cannot find a script able to handle it.

    Thanks anyway

    Reply

Trackbacks/Pingbacks

  1. UDF to Convert Numbers to Letters - Spell Currency in Words - […] Calculate MOD of Large Numbers in Excel […]

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest