Geek asks and answers

How can I express NaN values ​​in Excel?

I am writing a .NET program that creates an XML document that opens in Excel. A cell usually looks like this:

    <ss:Cell>
      <ss:Data ss:Type="Number">123</ss:Data>
    </ss:Cell>

But sometimes the meaning I'm writing is either NaN, DIV / 0, or INF. In these cases, 123 will be replaced with this text value, and Excel will not open the file due to text.

I don’t think Excel has constants, so I’m not sure how to handle this siutation, except to set the value to “-1” instead. Any suggestions?

+5
source share
1 answer

Here are some error codes that Excel knows about, stored in XML format:

<Cell><Data ss:Type="Error">#DIV/0!</Data></Cell>
<Cell><Data ss:Type="Error">#NUM!</Data></Cell>
<Cell><Data ss:Type="Error">#VALUE!</Data></Cell>
<Cell><Data ss:Type="Error">#N/A</Data></Cell>
<Cell><Data ss:Type="Error">#NAME?</Data></Cell>
<Cell><Data ss:Type="Error">#REF!</Data></Cell>
<Cell><Data ss:Type="Error">#NULL!</Data></Cell>

Only the first two are important for your question, but I added the rest for the sake of completeness.

, , NaN, DIV/0, INF

DIV/0 #DIV/0!. , Excel NaN INF, #NUM!. , #NUM!, , - .

=ASIN(2)
=EXP(EXP(10))

, : Excel ( ) . , .

+5

More articles:


All Articles