This article presents my first version of my free Excel Add-In (Version 0.75 Build 151110) for calculating large numbers in Excel. The Add-In (you can download the file at the end of this article) currently implements functions for the addition, subtraction, multiplication and exponentiation of large numbers. The code in the Add-In is password protected; however you can unprotect the VBA project by using the password “excel-ticker” (without the quotes). Please also read the copyright notice and license terms in the last section of this article.
The Add-In is also digitally signed with a certificate which was issued to me by the company “TC TrustCenter GmbH, Germany”. So, only files including this digital signature have been created and published by me.
Currently, the Add-In can only perform calculations with integer numbers, an extension for calculating decimal numbers is in progress. Additionally, the Add-In will later provide more functions, such as the division or calculation of special formulas like “X ^ Y Mod N”. These new functions will use more complex algorithms based on the research from Donald Knuth and derivatives from his algorithms.
Naming conventions of the functions in the Add-In
The Add-In includes the functions described below, which can be accessed, for example, through the category “User defined” of the select function box in Excel. Each function of the Add-In is available in English, German and French.
I named the Add-In functions similar to Excel functions having the same purpose. For example, you can use the Excel function “SUM()” for adding numbers. The function for adding to large numbers is named “LNSUM()”, where “LN” means “Large number”. The German and French function names in my Add-In are corresponding to the localized Excel functions with a localized prefix: “GZSUMME()” in German and “GNSOMME()” in French, where “GZ = Große Zahl” and “GN = Grand nombre”.
The arguments used in my functions are also named in a similar manner to the Excel functions. For example when using “LNSUM()”, the arguments are “Number1”, “Number2, “Number3”, etc. like in the function “SUM()”. However, my functions for adding and multiplying numbers only allow up to eight arguments instead of 30 arguments in the corresponding Excel functions.
Important note: a cell in Excel can contain a maximum of 32,767 characters. If the result of a calculation from my functions exceeds this length, then the error #Value is shown in that cell. Such case can occur relatively quickly when using large exponents in an exponentiation. For example the result of the calculation of 4 ^ 123456 contains 74,328 characters.
Addition and Subtraction of large numbers by using LNSUM()
As mentioned before, the function “LNSUM()” can contain up to eight arguments. It is allowed to specify a large number in an argument by using its representation as a value, a string, a cell reference or a range. Example: =LNSUM($C$1; $D$15:$E$20; 1234; “56789“). If the concerned cells are containing numbers, then the function should deliver a correct result. Empty cells are treated as zeros. However, if one cell contains a text, which cannot be converted to a number, then the function “LNSUM()” fails. If you would like to subtract to numbers, you may prepend the sign before the number.
Multiplication of large numbers by using LNPRODUCT()
Similar to “LNSUM()”, the function “LNPRODUCT()” allows specifying up to eight arguments. If a range is specified in an argument, then the values of all cells of this range are multiplied. Empty cells are treated as one in this case. Therefore, this function can quickly generate very large numbers. Example: =LNPRODUCT($C$1; $D$15:$E$20; 1234; “56789“) multiplies the value from cell $C$1 with all values from each cell in $D$15:$E$20 and 1234 and 56789. In one cell contains a text, which cannot be converted to a number, then the functions also fails.
Please note that, even the VBA code is using the very fast Karatsuba algorithm for the multiplications, I recommend you to not excessively use large number multiplications because the calculations are done sequentially for each function used in a sheet.
Exponentiation of large numbers by using LNPOWER()
This function works similar to the Excel function “POWER()” and therefore expects two arguments; the first one is the number to be powered and the second is the exponent. Please use this function with care; a too large exponent can lead to long calculation times. For example, calculating “5 ^ 12345” takes approximately 30 seconds on my computer. PS: if I calculate “80 ^ 200”, the result appears instantly, demonstrating that the algorithm is really fast enough for usual high power calculations.
Code for the functions in this Add-In
I had first considered describing the code of the functions in this section. However, this would have resulted in a very long article and mixed with code and functionality descriptions for the users of my Add-In. So, I decided to publish a detailed description of the code in the next article.
Copyrights and license terms for the Add-In
The producer – that’s me – provide to the user a limited, non-exclusive, free license for this product. The sole purpose of the Add-In is the calculation of large numbers in Microsoft Excel.
No warranty is given by the producer that the product is error free, does fit to all your requirements or will work with no interruptions. Furthermore, no warranty is given that errors will be corrected, that updates will be available or that new versions will be published. You will use the Add-In at your own risk; this includes any damage or loss of data resulting from the use of this software.
This Add-In may not be sold, rented, loaned, leased or commercially distributed in any other way. The product may definitively not be linked or distributed on fee-based websites. The product may not be offered on electronic or printed media without my explicit written agreement. This also applies to the code.
You are free to use the code in the Add-In for your own personal or internal company projects without any commercialization goals. However, the information about the author and other persons mentioned in the code may not be removed or changed.
The producer owns the title, copyright and all other commercial and non-commercial rights to this product. The use of this product grants you no rights to trademarks or service marks of the producer mentioned above.
Finally I would particularly like to thank Andreas Entenmann for supporting me when testing the Add-In.
» Download the Add-In for calculating large numbers in Excel
585
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
