Excel Large Number Arithmetic with Python

Introduction

When it comes to numbers, MS Excel follows the IEEE 754 standard (Floating-point arithmetic may give inaccurate result in Excel - Office). One of the consequences is that Excel maintains 15 significant digits in precision.

So, if we treat a credit card number as such and enter a 16-digit Mastercard or Visa number, the last digit will become zero. Enter 1234567890123456 and the result will be 1234567890123450.

Similarly, if we add two 15-digit numbers such that the result is 16 digits, we will get the wrong result.

123,456,789,012,345

+ 876,543,210,987,656 should be

1,000,000,000,000,001 but Excel will return 1,000,000,000,000,000.

One can search the 'Net for more examples where someone runs into this limitation. Instances when I've relied on large number arithmetic (LNA) have included

• Converting ids between base16 (UUID), base58 (popular in fintech), and base64, and
• Computations in math and statistics for work as well as questions posed by Project Euler (About - Project Euler)

Large Number Arithmetic in Excel

For starters, format the cell as text before entering the number (or precede the number with a single apostrophe)

Clearly, we cannot use Excel for computation on a number with more than 15 digits or one that yields a number larger than 15 digits. So, we resort to a programming language - VBA, Python, or JavaScript - to execute the computation and return the result as a string.

Several years ago, I wrote a set of VBA routines for the mathematical operations I needed at that time: addition, multiplication, power, and factorial. For this LNA code, see Large Number Arithmetic (tushar-mehta.com)

Recently, as part of testing the xlwings connector between Excel and Python, I wrote a couple of proof-of-concept functions in Python to support LNA in Excel. The first function was lna_add - as the name implies it returns the result of adding its two arguments. So, with the two 15-digit numbers mentioned in the introduction stored as text in L19 and L20, the formula below returns the correct result 1000000000000001.

=lna_add(L19,L20)        

Then, I decided to leverage Python's eval function to create an Excel-available function that executes any legitimate Python expression! The result was a one-liner, lna_eval. One example of how to use it: get the length of a large integer (stored as text in H21) with

=lna_eval("1+int(math.log10("&H21&"))")        

In the same vein, Excel loses precision when computing the factorial of 21 or any larger number. In Excel, =FACT(21) is

51090942171709400000. Whereas we get the result

51090942171709440000 with the below

=lna_eval("math.factorial(21)")        

Python code and xlwings

Installing xlwings and connecting it to a workbook is outside the scope of this article. However, it is well documented. Please visit Automate Excel with Python (Open Source and Free) (xlwings.org)

The Python code is straightforward

import xlwings as xw
@xw.func
def lna_eval(s):
? ? import math
? ? return(str(eval(s)))

@xw.func
def lna_add(f1, f2):
? ? from decimal import Decimal
? ? return(str(Decimal(f1)+Decimal(f2)))        

Summary

While Excel supports only 15 significant digits of precision, it is possible to work with larger numbers by storing them as text and using a programming language that supports such numbers. We can use Python as shown in this article and JavaScript is also an option.

My previous article: https://www.dhirubhai.net/pulse/ms-excel-recursive-functions-tushar-mehta