(C) 2009 - 2018 by Mourad Louha · All rights reserved

Calculation of very large numbers in Excel – Part 2 – Subtraction

In the first article of this series, I presented a VBA function for adding two large numbers represented by strings. This article presents a function for performing a subtraction for two large numbers. This function will, like the adding function, split the numbers into blocks, do the subtraction and then reassemble the blocks.

Subtraction of large numbers in Excel

The following picture shows two numbers X and Y which will be subtracted. Please note, that the presented function assumes that X is greater or equal Y. This is sufficient for the later use of the function as in case Y is greater X, we can inverse the subtraction to the statement “-(Y – X) with Y > X”.

VBA Large Numbers

The image above shows 5 blocks, each one having a length of 4 digits. A subtraction can be transformed to an addition of minuend (X) and the nines’ complement of the subtrahend (Y). The nines’ complement is calculated by the difference of the concerned digit to 9. Based on this logic, we can formulate that the subtraction corresponds to “Next exponentiation of 10 + minuend – subtrahend – carry = result with or without carry”.

The next exponentiation of 10 for a four digit number is 10000, so if we consider block “B5”, we obtain “10000 – 8574 + 2356 = 3872 < 10000”. We have in this case to subtract a carry in the next block. This logic is repeated from the right to the left until we have processed all blocks.

Similar to the code for the addition, we firstly check the arguments “x” and “y” for their lengths and we store the greatest length in the variable “lngLength”. The variable “lngPrecision” is then calculated in order to ensure an optimal value. And, if necessary, the code prepends zeros to the arguments.

The variable “dblCalc_Delta” holds the next exponentiation of 10, according to the schema “1 and N appended zeros, N = length of a block”. Then we enter the loop for calculating the differences for each block. After each intermediate value, a check, whether a carry is needed or not, is performed. We have also to keep in mind that no leading zeros are lost.

Finally, the code removes all unnecessary leading zeros. That’s all; the function for subtracting two large numbers is ready. In the next article, I will present a function for multiplying tow large numbers by using the Karatsuba’s algorithm.


No comments yet.

Leave a Reply