Excel Pivot wrong number format

Question

Dear All,

I have an MSAccess table which is storing some decimal numbers as below:

I have an issue because when I use the above table as source for an Excel PivotTable the value 15.775 becomes 15.7749996185303

Any way to solve this issue?

Thanks

Answer 1

I have an MSAccess table which is storing some decimal numbers [....] I have an issue because when I use the above table as source for an Excel PivotTable the value 15.775 becomes 15.7749996185303 [.] Any way to solve this issue?

I suspect the answer is "yes".

I am not familiar with MS Access, and I have very little experience with pivot tables.

But I believe I can offer some insight into the problem.

In a nutshell, I suspect that the field that displays 15.775 in MS Access is stored as type Single binary data in MS Access.

It should be type Double.

Explanation....

First, note that in Excel, all decimal numbers are stored as 64-bit binary floating-point values. And most decimal fractions must be approximated in binary.

When 15.775 is converted to 64BFP in Excel (and VBA), then converted to back to decimal, Excel displays 15.7750000000000 with 15 significant digits of precision. (Even with 17 significant digits.)

But 15.7749996185303 is the decimal approximation of 15.775 stored as a 32-bit binary floating-point value, then "converted" to a 64-bit binary floating-point value. (*)

So, the questions are:

1. When and how was 15.775 stored as a 32BFP value?

2. When and how was the 32BFP converted to 64BFP?

---

1. According to online sources, MS Access has both Single (32BFP) and Double (64BFP) binary data types.

So, perhaps all that needs to be done is: always use type Double for binary values, at least for fields that you might export to Excel.

(Arguably, VBA also has both Single and Double binary data types. Are you using VBA as part of the process to transfer MS Access data into Excel? I doubt it.)

However....

---

2. It appears that the conversion from 32BFP to the decimal string 15.7749996185303 was done in MS Access.

If the conversion were done in Excel, Excel would display 15.7749996185302 (!) instead. Ironically, that is due to a formatting defect in Excel. (**)

(Arguably, VBA does not have that formatting defect.)

---

TMI....

(*) The "conversion" from 32BFP to 64BFP is performed by the CPU, which simply appends binary zeroes to the "significand". Consequently, the result is usually not the same as entering a number with a decimal fraction directly as a 64BFP value.

(**) I have written extensively over the years about the formatting defect. LMK if you want a pointer to one of the explanations.

Answer 2

There is nothing to solve. It is a consequence of the way Excel stores numbers.