TM1 Forum

There is a discrepancy observed on Excel formula behaviors in TM1Web and Perspectives regarding a number wrapped in double quote marks.

We tried to use IF formula to compare whether a simple number (say, a number 5) equals to a specific value. We found that in Excel the IF statement will return FALSE when the Type of Data (i.e. a "number" or a "text") is different. Whilst in TM1Web it will return TRUE even if we compare a number (say, 5) with a text (i.e. “5”). Some examples are given in below table.

Has any one else ever seen this behavior before? Advice on how to gracefully write Excel formulas for TM1Web to avoid this is also appreciated.

TM1 version: 10.1.1
Excel: 2003 or 2007

Formula	Result in Excel	Result in TM1Web
=IF(9=9, "TRUE", "FALSE")	TRUE	TRUE
=IF(9="9", "TRUE", "FALSE")	FALSE	TRUE
=IF(9.01=9.01, "TRUE", "FALSE")	TRUE	TRUE
=IF(9.01="9.01", "TRUE", "FALSE")	FALSE	TRUE

This might work: That way its apples and apples. Note there's no need to quote the TRUE and FALSE outcomes otherwise you will be casting strings back to booleans.

Hmmm....that is interesting...have you tried this in 10.2.2 yet?

rmackenzie wrote:This might work:

Code: Select all

=IF(TEXT(A1,"@")=TEXT(B1,"@"),TRUE,FALSE)

That way its apples and apples. Note there's no need to quote the TRUE and FALSE outcomes otherwise you will be casting strings back to booleans.

Thanks Robin for the good code suggestion. What does the @ symbol do as placed in format parameter for TEXT function?

And yes, you are right, no need to add quote for TRUE and FALSE. That was just kept there when I replaced the original output texts.

blackhawk wrote:Hmmm....that is interesting...have you tried this in 10.2.2 yet?

Nope.... not yet. Anyone tried it please let us know.

John Zhang wrote:What does the @ symbol do as placed in format parameter for TEXT function?.

It's part of the syntax for custom number formats - in this case, it converts a number to a string. It's a bit like sticking an ' in front a number. For your problem, it is just saying compare text with text, not numbers with text which won't work, i.e. this is FALSE: But this is TRUE: Does this solve the TM1 Web problem? I don't know if TM1 Web will interpret all the different types of custom number formats that can be set in Excel itself.