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

Calculation of very large numbers in Excel – Part 5 – Add-In

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.

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.

VBA Large Numbers

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.

VBA Large Numbers

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.

VBA Large Numbers

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.

VBA Large Numbers

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

 

 
Comments
Mourad Louha

Hi Jane,

when opening the CSV file in Excel, the application tries to recognize the format of the data columns and therefore cuts off the last digits, as Excel can not handle more digits. You can try to import the CSV file and not directly opening it.

How, is depending on the Excel-Version you have. In older version, there is a text import assistant icon for that in the data tab.

In new Excel versions (Excel 2016), you can use PowerQuery from the same data tab. If you prefer to use the (legacy) import tool, it may be missing in the ribbon. But you can reactivate the legacy tool by going to the Excel options and check the appropriate checkbox in the data category of the options. Then, you will find the assistant in Data / Get data / Legacy wizards / From text (Legacy).

Once you have opened the legacy wizard, on the third page, after defining the separator behaviour in the pages before, you can define per column the data format. If you choose to have the large values as text, Excel will preserve the large numbers by setting them as text format in the sheet.

Hope this helps.

Best,
Mourad

Hi. I am interested to find a way of using numbers over 16 digits in excel as a csv file. Do you know if this is possible? At the moment all numbers show as 1.23457E+19 , but then If they are expanded they show with many 0000000’s at the end. Many thanks!

Hi Eric,

this is a very interesting article from S.A. Burr. Looking forward how this could be implemented in VBA. PS: if you like or needed, we can also communicate by e-mail. You can reach me at vba(at)maninweb(dot)de.

Happy holidays 🙂
Mourad

Hi Mourad,

I decided to name the functions LNSQRT and LNCUBERT (instead of LNCUBIC as this is more fitting).

The other news is that there was a paper written by S.A. Burr in April 1981 called COMPUTING CUBE ROOTS WHEN A FAST SQUARE ROOT IS AVAILABLE (https://ac.els-cdn.com/0898122182900414/1-s2.0-0898122182900414-main.pdf?_tid=2a01f98c-e85e-11e7-b7d2-00000aab0f6b&acdnat=1514087859_0a91fac316d40628ac6e5fd0e409ee02). Burr’s method converges faster than Newton’s method and will be used for calculating the cube root. Since Paul Zimmerman’s method (this is used in GMP, by the way, for square roots) will be used for the square root, this method will work quite well. The summary of Burr’s paper is that for the function, f(x) = x^^3 – a = 0, where a is the number we need to find the cube root, the following function, {X1} = (SQRT((4*a)/{X0}) + {X0})/3, will converge faster to the cube root than Newton’s method of {X1} = ((2*{X0})+[a/({X0}^^2)])/3, where {X0} is the initial guess. I have decided to use SQRT(SQRT(a)) as the initial guess as this will start of with the fourth root of a as a starting point and will converge faster than many other guesses. Since Karatsuba will be used internally to calculate SQRT it should work out nicely. The cube root will be made up up one sqrt, one multiplication and two divisions each iteration and it seems to converge about 6+ times faster for large numbers (in terms of iterations). Manually going through each iteration in excel (using the SQRT function and regular multiplication, addition and division), I tried finding the cube root of 10^^60 using both methods. Newton’s method (two multiplications and two divisions) took 58 iterations to produce the answer of 10^^20. Burr’s method took 9 iterations to produce the same answer. The difference between them is one sqrt using Burr’s method vs. one multiplication using Newton’s method. So hopefully using a fast sqrt of Karatsuba should make it faster with Burr’s method. I will have to do tests after I write the VBA code.

Cheers, Eric

Hi Eric,

thanks, sounds great!

Best,
Mourad

I will work on LNSQRT and LNCUBIC.

Cheers, Eric

Hi Eric,

much thanks for the info about the SQRT Algo from Karatsuba. I guess, it would be possible to implement the SQRT algo, but currently my time is very limited. SO I can’t promise you anything, meaning I don’t know when I have time to add the algo. If, then not before next year.

The VBA-Project is password protected, but the password is published above in this article. So, if you like to extend the code, please feel free to do it. It would be great, if you then share the new version.

Best,
Mourad

The following paper discusses Karatsuba Square Root and was published by Paul Zimmerman (May 24, 2006): https://hal.inria.fr/inria-00072854/PDF/RR-3805.pdf. This could be used for Public Function LNSQRT(Number As Variant) As Variant. I would have to figure out the best way to do Public Function LNCUBIC(Number As Variant) As Variant. We could assume positive real numbers (no complex numbers) only for the parameter and return values.

if it isn’t easy i can easily write one because i know the quadratic and cubic equations well. i would only calculate the real root for the cubic.

Thanks for this. One more question. i can’t seem to do square roots or cube roots by doing LNPOWER(1,1/2) or LNPOWER(1,1/3). Is there any way to quickly add that functionality ? It is the only other thing I require for my work.

Hi Eric,

thanks 🙂 The function however only calculates positive numbers. Please see here: http://www.excel-ticker.com/calculation-of-very-large-numbers-in-excel-part-4-exponentiation/ how the algo works.

But you can calculate the negative exponentiation by prepending a simple formule to LNPOWER. Example: =CHOOSE(1+MOD(B1,2),1,-1)*LNPOWER(A1,B1) where A1 = 1 and B1 = 3. Hope this helps.

Best,
Mourad

Hi, This is great. Only problem I am seeing is that if I do something like =LNPOWER(-1,3), I get a positive 1 and not a negative 1.

Hi Matt

thanks 🙂 Currently at this time I am not planning to extend the Add-In due to that I am involved in too many business projects. May be later, but unfortunartely I can not give any promise. I am not a mathematician, so by curiosity, which algorithm are you meaning? May be based on these ones https://en.wikipedia.org/wiki/Modular_arithmetic? A link e.g. to wikipedia could be helpful.

Best, Mourad

Hi Mourad,
This is really useful, thanks!
Any progress with providing more functions, such as the division or calculation of special formulas like “X ^ Y Mod N” ?
Cheers,
Matt

Hello Alex,

ok, I sent you an unprotected version to your e-mail address, you have indicated here. Please tell me then, if you got the file. Please note, that the code contains Windows API functions for the info dialogue. So, you should remove these API functions and/or the dialogue as they will not work on a Mac. Please also see the previous comments above.

Best, Mourad

Thanks for prompt reply, Mourad. I am on a Mac, MS 2011; I’ve tried excel-ticker and variations thereof, but without success!

Hi Alex,

please try excel-ticker as password. Should work. Note: the code signature expired, so that some newer Office versions firstly deactivate the macros. After getting to the code, add e.g. somewhere a space in a comment line of the code and save the file for removing the signature. Or remove the signature by using the tools menu from the VBA Editor.

Best, Mourad

Hi Mourad: Thank you for your work. The password does not work for me. How do you suggest I proceed?

Best,
AP

Hello onu

first, no links to sites which are not related to Excel please. So I removed your link to the site. You can enter an single apostroph when entering large numbers. Example ‘123456789012345… The apostroph says Excel to keep the value as entered. Another possibility is to format the number as text.

Best regards, Mourad

How can I put more then 19 digit? If I select text then it show as like 1e1548545f54515e22 like this. How can I fix this problem?

Thank you

Hello Igor,

first, sorry for the delay in approving the comment; we have just moved to a new home and therefore I was too busy.

Yes, you are right, thanks for pointing out this bug. If necessary, you can change the code by replacing/add a small section. In the module (the password is excel-ticker for the VBA-Project) MLP_Functions, search the function mlfhExponentiation. At the bottom of the function add an If-statement between If blnNumber And blnExponent Then … End If for checking the power value. After the change, the code looks like:

'     Calculate...
      If blnNumber And blnExponent Then
        If CLng(vntExponent) <> 0 Then
          vntResult = mlfpNumbersCalculate(CStr(vntNumber), _
                      CStr(vntExponent), mlcpNumbersExponentiation, _
                      CVErr(xlErrValue))
          
        Else
          vntResult = 1
        End If
      Else
        vntResult = CVErr(xlErrValue)
      End If

Best, Mourad

Hi Mourad, thanks a lot! This was really helpful for solving my problem.
I found a little “glitch” in the code which you might find interesting to look at. Using LNPOWER() with power=0 returns null instead of 1. Of course this is only a minor thing compared to the superb job your code is doing.
Keep up the good work!

Regards, Igor

Hi Mourad, Thanks very much for your response. I’ll download the file you suggested and make the appropriate changes. Will let you know how I go. Thanks again.

Regards, Robert

Hello Robert,

I don’t know if VBA is included in Office 2008 for Mac. If so, then you can use this Add-In above. However, the Windows API functions should be removed. Delete the MLP_Api Module and compile the project. On each line, you get an error, it was a call to functions from the deleted module. Just also delete the line. After that, it should be possible to use the Add-In functions as described above. If Excel 2008 for Mac has not VBA included, then I am afraid, that I can not help you and I suggest to ask in a Office for Mac forum.

Regards, Mourad

Hi Mourad,

I’m looking for assistance on Excel from Office 2008 for MAC. I’m wanting to add integers of 16 digits in length without having the rounding errors associated with the 15 digit limitation of excel. I can have the result displayed in text. If you could offer any assistance that would be greatly appreciated.

Regards,

Robert.

Hi Telesfor,

thanks, fine that all works now 🙂

Best regards, Mourad

Works perfect ! It requires to replace not exiting at MEC Excel CDec() by CDbl() but still it is doing a great job of computing. The performance is OK (I am using Mac Book Pro with i7 4x core).

Karatsuba was really a great mathematician. Thank you.

Hello Telesfor,

Ok, I sent the changed file to you at the e-mail address you leaved here for your comment.

Regards, Mourad

I can not edit Karatsuba.xla under MAC Excel VB Editor. Whatever I try it request me for password and “excel-ticker” w/o quotes does not work. Can you assist please ?

Hi Telesfor W,

you can remove the API functions by yourself. Just delete the module MLP_Api and remove the line mlfpApiDeframe Me, True in the MLF_Info dialogue.

Regards, Mourad

I am interesting to try it on MAC Excel. Can you provide version without WinAPI please ?

Hello Riad,

thanks, glad I could help 🙂

Best Regards, Mourad

Mourad, you sooo saved my day – thank you so much!!

Best Regards, Riad

Mourad Louha

Hello Mark,

the tool is for performing basic calculations with large numbers. I think, for converting big dec. numbers to hex, an extra function should be implemented, which can be completely independent from this tool.

Best regards, Mourad

Is there a way to use this add-in to do a Decimal to Hex conversion on 18 digit numbers?

Hello GDwitt,

you can’t calculate in Excel with number with more than 15 digits. But the Add-Ins performs this by using text and internally transforming the text into numbers. So, if you user LNSUM(Arg1;Arg2) the result is the sum of both arguments. Hope, this helps.

Regards, Mourad

I get the #value error when I try lnsum of any value over 16 digits.
I need to calculate so can’t use the “” or text work around.
Do you have any updated advice?

Mourad Louha

Hi huyns,

123456789123456789 can not be recognized as a number. You may pass the value as string, example: LNSUM(1,2,3,4,”123456789123456789″).

Regards, Mourad

Hi Mourad Louha,
I’ve used function LNSUM like that LNSUM(1,2,3,4,123456789123456789) and i’ve get #VALUE for result. Note Arg5 has 18 characters.
Why?

Hi Chris,

thanks for you comment and suggestions. The Add-In was originally implemented for Excel 32 Bit. If you run it in Excel 64 Bit, you’ll get the compiler errors you mentionned. In 64 Bit, the API declarations may be changed. To get a version which can be run on different platforms, you can also use the compiler constants as described here: http://msdn.microsoft.com/en-us/library/ee691831%28office.14%29.aspx

When someone changes the code, the certificate which signs the orginal Add-In, will be lost and Excel will inform you about this. The certificate I used had the purpose to prove the origin of the file (me). There is no difference in the code with the unsigned file and so no security concerns should be appear; excepted that some companies does not allow the execution of unsigned files.

Best Regards, Mourad

I ran into a problem where I kept getting an error referring to MLP_API.
I’m running Office 2010 64bit and believe that was the root cause.
I managed to fix it by editing the Karatsuba.xla file. To do this: After adding Taratsuba.xla to your Excel add-ins – open VBA (alt-F11) from Excel. Open MLP_Maninweb (Karatsuba.xla) and enter the password mentioned on this page. Expand Modules, and open MLP_Api. Notice that if you run Debug > Compile MLP_Maninweb, you get an error mentioning PtrSafe. Just add “PtrSafe” in-between Declare and Sub for the five API function calls (apiSleep, apiDrawMenuBar, apiFindWindow, apiGetWindowLong, and apiSetWindowLong)

Example:
Private Declare Sub apiSleep Lib “kernel32” _
Alias “Sleep” (ByVal a As Long)
becomes
Private Declare PtrSafe Sub apiSleep Lib “kernel32” _
Alias “Sleep” (ByVal a As Long)

Then save the file. You will be prompted about something regarding signature – obviously this change invalidates it.

Now, there may be security issues with doing this, so I recommend that anyone considering doing this looks into security concerns – especially in a business environment. So be warned.

Hi Petr, Thanks 🙂

Hi Mourad, thanks a lot! It helped me to solve a problem I didn’t know what to do with. Appreciated!

Hi Ali…

Thanks for the offer, would be really great. I will notice you per e-Mail, when I am ready.

Best Regards 🙂

I am willing to test both integer and decimal add ins on mac (Numbers & Excel) . Just let me know.

Hi Ali…

Thanks; fine that the Add-In helps. Unfortunately, I did not got the time to continue the implementation for decimal numbers; however this is still on my list and I hope to release it soon.

Best Regards 🙂

Hi Mourad,

This is a very useful add in, thanks for sharing. Have you developed the one for decimal numbers yet?

Hi Andrew…

this Add-In uses some Windows API functions, which are not available on a Mac. If you are interested and will help testing on Mac (I haven’t a Mac), I can build a version without the API functions, which then *normally* should work on a Mac.

Regards 🙂

Does this add-in only work for PC-based Excel? I have Mac Excel and it gives me a compile error….

Hi Keith,

thanks for the suggestion, sounds very interesting 🙂

Regards

You should check out http://tukhi.com/tukhi_fun.html. That page has a link to an add-in that hooks up the GNU multi-precision integer library.

Leave a Reply