My XLF program is showing a NaN error when it should not. There is an input text pointing to a particular cell and another text label points to that same cell, and is showing the NaN when I preview the SWF. The FAQ on the Xcelsius support site states that when a NaN error is showing that it is expecting a numeric value when there is none. This confuses me because the cell in question is supposed to display text, not a number, based on the results from other cells. Similarly, my input text is not configured to be a numeric format.
Here is the actual formula in the cell which appears to be triggering the NaN error in Xcelsius:
<code>
=IF(OR(ENGINE!B4=0,ENGINE!B7=0,ENGINE!B8=0,ENGINE!B9=0,ENGINE!B10=0), "Please fill in the required fields", ENGINE!B31&" "&ENGINE!B32)
</code>
Thanks in advance.
'''Update'''
The formula I posted above is '''not''' actually the problem. In fact, it is referencing another cell that appears to be the source of the NaN.
Below is a table showing seven successive formulas. The origin of the NaN error seems to be related to #3 (see highlighted row).
Excel is not having any issues and my spreadsheet only contains functions that are supported by Xcelsius.
{| cellspacing="0" border="1" cellpadding="2" background="white"
|- bgcolor="#CCCCFF"
!width="20"|Cells
!width="70"|Descriptions
!width="70"|Formulas
|-bgcolor="#F7F8FF"
|'''B23'''
|Add up four values B10, B11 and B12
|=IF(OR(B8=0,B9=0,B10=0),"",SUM('Input and Report'!B10,'Input and Report'!B11,'Input and Report'!B12))
|- bgcolor="#F7F8FF"
|'''B24'''
|Show 1st digit of result in B23
|=IF(LEN(B23),LEFT(B23)+0,"")
|- bgcolor="#FFFF93"
|'''B25'''
|Show 2nd digit of result in B23
|=IF(LEN(B23)>1,MID(B23,2,1)+0,"")
|- bgcolor="#F7F8FF"
|'''B26'''
|Show 3rd digit of result in B23
|=IF(LEN(B23)>2,MID(B23,3,1)+0,"")
|- bgcolor="#F7F8FF"
|'''B27'''
|Show 4th digit of result in B23
|=IF(LEN(B23)>3,MID(B23,4,1)+0,"")
|- bgcolor="#F7F8FF"
|'''B28'''
|Sum of these four digits
|=SUM(B24:B27)
|- bgcolor="#F7F8FF"
|'''B29'''
|Do something fancy to the sum
|=IF(B28<>"",IF(B28<23,MOD(B28,22),LEFT(B28)+RIGHT(B28,1)),"")
|}
I've traced it a bit further, and I suspect it has to do with the use of adding a zero. This is actually a hard requirement for me given the type of calculations I am doing, so I'm not sure how else to accomplish this.
<strong>=MID(B23,2,1)+0</strong>
Add a comment