Cell references in expressions

To include a cell reference within an expression, you must specify the expression in an Excel cell. (In task-pane fields, you can only use a cell reference to specify the entry for the entire field.)

In the Excel cell, enter the expression as an Excel formula following this approach:

• Split the expression into the strings before and after the cell references.

• Concatenate all the strings with the ampersand (&) calculation operator. At calculation, Excel combines the strings before and after the ampersand to create a single string.

Examples

• Cell reference for single value

  Suppose you want an expression that returns true when the PI point specified in cell B6 is greater than 0:

  'Point in B6' > 0

  In the cell where you want to store the expression, enter:

  ="'" & B6 & "' > 0"

  Excel resolves the formula based on the value of the cell B6, such as the point sinusoid. In a PI DataLink function, you can reference this cell in any expression field that requires a Boolean expression.

  

• Cell references for multiple inputs to performance-equation function

  Suppose you want an expression that uses the TimeGT performance-equation function with inputs from an Excel worksheet.

  

  This function finds the total time that a PI point is above a particular value. In the worksheet, cell B6 contains the point, B7 the start time, B8 the end time, and B9 the value. The start and end times are entered as PI time expressions. The equivalent expression is:

  TimeGT('Point in B6','Time in B7','Time in B8',Value in B9)

  In the cell where you want to store the expression, enter:

  = "TimeGT('" & B6 & "','" & B7 & "','" & B8 & "'," & B9 & ")"

  Excel resolves the formula based on the value of the cells.

  

• Excel-function and cell-reference inputs to performance equation function

  Suppose you want to use the same performance-equation function but the time inputs use a non-PI time format.

  

  In this case, the time is entered as a string in a specific format. You can use the Excel TEXT function to convert the time strings into a date. The equivalent expression is:

  TimeGT('Point in B14','TEXT(B15,"DD-MMM-YYYY HH:MM:SS")','TEXT(B16,"DD-MMM-YYYY HH:MM:SS")',Value in B17)

  In the cell where you want to store the expression, enter:

  ="TimeGT('" & B14 & "','" & TEXT(B15,"DD-MMM-YYYY HH:MM:SS") & "','" & TEXT(B16,"DD-MMM-YYYY HH:MM:SS") & "'," & B17 & ")"

  Excel resolves the formula based on the value of the cells.