Menu
I'm writing a .NET program which generates an XML document that is opened in Excel. The cell's typically look like:
<ss:Cell>
<ss:Data ss:Type="Number">123</ss:Data>
</ss:Cell>
But occasionly the value I'm writing has either NaN, DIV/0, or INF. In these cases 123 would be replaced by that text value and Excel won't open the file because of the text.
I don't think Excel has constants, so I'm unsure of how to handle this siutation, other than put a "-1" value in instead. Any suggestions?
688
To write NaNs to Excel, they must be explicitly written as strings like 'NaN'. To achieve the same, you can convert your data matrix to a cell and replace all NaNs with 'NaN' before writing to Excel as shown below: % A is the Data Matrix containing NaNs.
Excel NA Function. 1 When other formulas refer to cells that contain #N/A, they also return #N/A. 2 NA takes no arguments, but you must provide empty parentheses. 3 You can also enter the value #N/A directly into a cell as text.
How to Replace #N/A Values in Excel (With Examples) You can use the following basic syntax to replace #N/A values in Excel with either zeros or blanks: #replace #N/A with zero =IFERROR (FORMULA, "0") #replace #N/A with blank =IFERROR (FORMULA, "")
Excel does not know how to interpret the NaNs in the middle of a Numeric Matrix and hence treats them as blank spaces. To write NaNs to Excel, they must be explicitly written as strings like 'NaN'. To achieve the same, you can convert your data matrix to a cell and replace all NaNs with 'NaN' before writing to Excel as shown below:
Here are the different 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 relevant for your question, but I added the others for the sake of completeness.
But occasionly the value I'm writing has either NaN, DIV/0, or INF
DIV/0 maps to #DIV/0!. I do not think there is a distinction between NaN or INF in Excel, they both seem to be expressed as #NUM!. For example, both of the following formulas resulted in #NUM!, even though the first one is an invalid number whereas the second one is just very large.
=ASIN(2)
=EXP(EXP(10))
Interesting, tough hardly useful: Excel allows you to enter these literal error values (including all punctuation) into cells on your worksheet without a problem. In a sense, these are built-in constants.
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
