Geek Answers Handbook

Can anyone confirm how Microsoft Excel 2007 internally represents numbers?

I know the IEEE 754 floating point standard by heart since I needed to study it for an exam. I know exactly how floating point numbers are used and the problems they may have. I can manually perform any operation on the binary representation of floating point numbers.

However, I have not found a single source that explicitly states that excel uses 64-bit floating point numbers to internally represent each individual “type” in excel, with the exception of text. I have no idea if some of the types use signed or unsigned integers, and some use a 64-bit floating point.

I found literally trillions of articles that 1) describe floating point numbers, and then 2) talk about being careful with excel because of floating point numbers. I did not find a single statement saying "all types are 64-bit floating point numbers, except for text." I have not found a single statement saying that “changing a cell type only changes its visual representation, and not its internal representation, unless you change the text type to some other type that is not text, or you change which "or another type, which is not text to text."

This is literally all I want to know, and it is so simple and axiomatic that I am amazed that I can find trillions of articles and pages that communicate with these statements, but do not report them directly.

+5
source share
3 answers

Excel 2007 supports the OpenXML format, which is a ZIP file (.XLSX) containing a bunch of XML files. There is an SDK for working with the OpenXML format, which you can get for it here and download it here .

Basically, the numbers are stored as plain text inside the element, so if cell A1 is 42 and cell A2 is 81.56 in the user interface, the XML will look like this:

<row r="1" spans="1:2">
    <c r="A1">
        <v>42</v>
    </c>
    <c r="B1">
        <v>81.569999999999993</v>
    </c>
</row>

When working with OpenXML, I would strongly recommend using the SDK, and not immediately after it.

+3
source

This page contains a link to Excel internal data types.

+3

He kept the numbers as doubles.

-1
source

More articles:


All Articles