## Excel Trick to Calculate MOD of Big Numbers.

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

I have also explained shortcomings of MOD formula in Excel and how to overcome those shortcomings.

Lets start with the basics – What is MOD function?

## What is MOD Function in Excel

MOD is short form of Modulus. Modulus is a function which 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 MOD of any 2 numbers.

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

### Important Note:

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

## 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 which can calculate MOD of any number of digits

## VBA Code to Calculate MOD of Big Numbers

To overcome the MOD function error, I came up with 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 school days – How to divide a big number.

So here if what you do..

**Step 1 : **First pick up first n length of digit from the Large Number provided (Where n = Length of Divisor + 1) – Just to make sure that big number is divided with small number.

**Step 2 : **Now for this subset of 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 again…

Refer the image below for more clarity

With the above process, it is clear that, if we automate the above step in VBA then, there you can calculate MOD of any big number. Because at a time, you are dividing a subset of the whole number. Number of iteration 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 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.

JaimeMay 27, 2019 at 12:44 pmMuy buena explicación y genial para ejercitarse en macros