IBM Planning Analytics: Workarounds for Unsupported Excel Functions in TM1 Web
IBM Planning Analytics: Workarounds for Unsupported Excel Functions in TM1 Web

This is a guest blog post from Revelwood’s Nina Gordy. 

Did you know there are a number of workarounds available for Excel functions that are not supported by TM1 Web? This blog post is the second in a series discussing these workarounds. Below, I’ll describe ways to accomplish the same results using functions that are compatible online.

 

Function: CELL

Description: Returns information about the formatting, location, or contents of a cell

Please note that workarounds only exist for some of the information supported by the CELL function.

A B C D
1 Input Original Result Compatible with TM1 Web
2 1 CELL(“address”,A2) $A$2 ADDRESS(2,1)
3 CELL(“col”,A2) 1 COLUMN(A2)
11 CELL(“row”,A2) 2 ROW(A2)

 

Function: ISLOGICAL

Description: Returns TRUE if the value is a logical value

A B C D
1 Input Original Result Compatible with TM1 Web
2 1 ISLOGICAL(A2) FALSE IF(OR(A2=TRUE,TEXT(A2,””)=”FALSE”),TRUE,FALSE)

 

Function: N

Description: Returns a value converted to a number

A B C D
1 Input Original Result Compatible with TM1 Web
2 1/1/2018 N(A2) 43101 VALUE(A2)
3 NUMBERVALUE(A2)

 

Function: QUOTIENT

Description: Returns the integer portion of a division

A B C D
1 Input Original Result Compatible with TM1 Web
2 5 QUOTIENT(A1/A2) 2 FLOOR.MATH(A1/A2)
3 2

 

Function: SQRTPI

Description: Returns the square root of (number * pi)

A B C D
1 Input Original Result Compatible with TM1 Web
2 4 SQRTPI(A2) 3.544907702 SQRT(A2*PI())

 

IBM’s documentation on Excel functions that are not supported by TM1 Web can be found here.

IBM Planning Analytics, which TM1 is the engine for,  is full of new features and functionality. Not sure where to start?  Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!


Never miss another post.

Sign Up for Our Newsletter.