NaN error appears when it should not

[Former Member]

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.

[Former Member]

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>

[Former Member]

'''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)),"")

|}

[Former Member]

Fixed! Xcelsius Support had helped me with this a long time back, but I had shelved the project due to various factors including funding, and lost site of this little gotcha.

In case anyone cares, cell B23 actually contains a value and not text. This is fine in Excel but apparently not in Xcelsius. So I had to add an additional function to convert it into text and then my successive functions were all accepted.