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 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!
