Worksheet Functions

IBM® Planning Analytics Worksheet functions return a numeric or string value. You can use TM1® worksheet functions anywhere in an IBM Planning Analytics for Microsoft Excel or TM1 Perspectives worksheet.

To access these functions in Microsoft Excel, choose Formulas > Insert Function.

Note: If you are using Planning Analytics for Microsoft Excel, you must first enable the IBM Cognos® Office Reporting TM1 addin.

If a worksheet function references an object on a remote server, you must prefix the object with the server name and a colon. For example, to refer to the SalesCube cube on the SData server, use SData:SalesCube. You must be connected to the server referenced by the function to receive accurate values in your worksheet. If you are not connected to the server, TM1 worksheet functions return *KEY_ERR.

TM1 worksheet functions accept strings, values, or cell references as arguments. Strings must be enclosed in quotation marks. Numeric element names must be enclosed in double quotation marks. For example ""14357"". Cell references must refer to valid arguments for a given function. You can use standard conventions for absolute and relative cell references in worksheet functions.

Due to a limitation with Microsoft Excel, worksheet functions can contain no more than 30 arguments. When you construct a cube reference, one argument must be the cube name, which leaves 29 arguments for specifying the cube dimensions.

If you record a worksheet macro in Microsoft Excel that includes TM1 functionality, the resulting macro may include undocumented TM1 worksheet functions. We may, however, modify or discontinue these undocumented functions in future releases without notification.

Worksheet functions cannot be used in TM1 rules or in TurboIntegrator processes.