how are numbers represented as binary in excel

Question

having problems with importing numbers in excel into quicken. seems quicken truncates the binary representation and produces a number off by a penny or so. i want to understand how the numbers are stored in excel so i can understand how the truncation (vs roundoff) works in quicken

Answer 1

In Excel, numbers are represented using a format called binary floating-point notation, which is based on the IEEE 754 specification. This means that numbers are stored in a binary format that can sometimes lead to precision issues due to the way certain decimal numbers are converted into binary.

For example, the decimal fraction 0.1 cannot be represented exactly in binary, leading to a small rounding error when it is stored. This is because 0.1 in binary becomes a repeating binary fraction, which cannot be stored in a finite amount of space. As a result, when you perform calculations involving these numbers, the inaccuracies can propagate, leading to results that may differ slightly from what you expect.

When you import numbers from Excel into another application like Quicken, these small inaccuracies can manifest as truncation or rounding errors, which might explain why you are seeing numbers that are off by a penny. The specific representation of a number in Excel can sometimes be slightly different from what is displayed in the user interface, as seen in the case where 0.049 is stored as 0.049000000000000002 in the underlying data.

To mitigate these issues, Excel provides an option called "Precision as displayed," which forces Excel to store numbers as they are displayed in the worksheet. However, using this option can lead to loss of precision, so it should be used with caution.

Understanding these nuances in how Excel stores and calculates numbers can help you better manage the data when importing into other software like Quicken.

Answer 2

Thanks for reaching out Benjamin,

I understand the confusion. Excel uses a specific format for storing numbers, which can lead to small differences when exporting to Quicken.

These differences might appear as a penny off due to how Excel handles decimals in binary form.

First, ensure that the numbers in Excel don't have hidden decimal places by showing full precision. Also, tell me if you're exporting as a CSV file or via a direct link, as the export format can affect the values.

If you let me know the format, I can explain how to round the numbers to two decimal places in Excel before importing them into Quicken. Would you like a guide on how to do this?

Regards,

Rez

Answer 3

One way to avoid this problem is to convert all values to cents (5.23 become 523) before transfer and convert them back in Quicken.. Integers up to at least 14 digits are represented exactly. This will let you process up to $100B without problems.