Thread: Excel precision through queries

Hello,

I have a program that uses a query to grab data from overseas. I have an issue where some precision is apparently lost and I was curious if there was a way around this.

As an example of what is going on I have a specific piece of data I am tracking. The value is 14.9 in the SQL database and it's datatype there is a real of length 4. After grabbing this item from the query I checked the record set and the value is still 14.9. After it reaches the worksheet though it is displayed as 14.89999962. The destination cell is currently formatted as General but I have tried Text as well. Both produce the same results. As this tool is very general I would not have it formatted as a number as I don't know what I am getting back exactly.

I know computers store real/single values inprecisely but I see it everywhere else as 14.9. I would think Excel would display what I am seeing. Is there anyway around this short of formatting the cell as number with 1 decimal place?

Thanks in advance.

I assume the data in the SQL database is single-precision. I say that because 14.89999962 is the closest single-precision representation of 14.9 (hex 416E6666; note the repeating decimal).

Shown in Excel, you see the double-precision representation of that Single converted to a Double. A Double can't represent 14.9 exactly either (note the repeating decimal CCC..., rounded in last position below), but there's a lot of code space between the two numbers in double precision (see below)

Code:

      -----A----- ---B---- -------C--------
  1     Number     Single       Double     
  2   14.89999962 416E6666 402DCCCCC00C9FF6
  3          14.9 416E6666 402DCCCCCCCCCCCD

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes