Archer Dynamics

If you work in banking, you probably already know about this, but in case you haven’t stumbled upon this hidden limit, try the below experiment with me…

Analysts at banks must hate Excel for this

I know I did. Don’t believe me? Try this right now: type these numbers into Excel:

111111111111111 (15 ones)
1111111111111111 (16 ones)
11111111111111111 (17 ones)

Then watch what happens next when you change the format from exponential to a regular number. The first stays intact, the second becomes 1111111111111110. The third? 11111111111111100. Excel just corrupted your data and it didn’t even warn you.

This isn’t a bug. It’s IEEE754, the floating point standard that Excel uses and which trades precision for calculation speed. What does it do? Excel can store only 15 significant numbers, so if you add more, Excel will start rounding and replace all digits after the 15th one with zeroes!

Back to the banking comment made above:

If you work with credit card numbers, DO NOT store them as numbers! Ever. Excel will replace the 16th digit with a zero.

AmEx customers must be very happy with their shiny green cards and 15-digit numbers!

Real solutions that actually work

There are some solutions that kind of work and some solutions which are a no brainer.

- Excel: format credit card numbers or long IDs as text only
- PowerPivot - it's Excel underneath - use text
- Access - same as Excel - format long numbers as text
- Alteryx - handles any length, but beware exportsto CSV/Excel
- PostgeSQL - NUMERIC can handle over 130k digits
- SQL Server - DECIMAL can handle 38 digits (I think?)
- BigQuery - I don't even know if it has any limit at all
- SalesForce, and other modern platforms can handle more than
  Excel, but still store long IDs as a string, not a number.

The lesson?

Excel is a big honking calculator that can do nice tables and dashboards and project trackers, but it’s not a database. Excel is an amazing tool, a very versatile tool, but as all tools – they have their perfect use cases and also situations where they should be avoided.

Just because you can tenderize a steak with a 12-lb sledgehammer doesn’t mean that you should.

Need Help?

Need help streamlining your processes or solving tricky business problems? I offer one-on-one consultations to get you unstuck fast. Book a free consultation with me today at goarcherdynamics.com.

Want more practical tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for newsletter updates: goarcherdynamics.com