Use the HISTORIAN.SINGLEVALUE function
- Last UpdatedMar 05, 2025
- 2 minute read
The HISTORIAN.SINGLEVALUE function retrieves single values for a specified list of tags.
Syntax
HISTORIAN.SINGLEVALUE(TagList, ValueType, Time, Details, Direction, DiscreteFormat)
-
TagList - Required. The range of cells containing the list of tag names you want to retrieve information about.
-
ValueType - Required. The type of values to retrieve. The following types are supported: Interpolated, EndBound (next with time), StartBound (previous), or previouswithtime.
-
Time - Required. The date and time for which to retrieve the tag values, expressed as a serial date number.
-
Details - Required. A comma-separated list of additional properties to retrieve for the selected tags. The following properties are supported: TagName, DateTime, and OPCQuality.
-
Direction - Optional. If TRUE, the data is listed in rows. If FALSE, the data is listed in columns. If no value is specified, the default value of TRUE (rows) is used.
-
DiscreteFormat - Optional. The format in which to display discrete values. The following formats are supported: label, numeric, or boolean. If no value is specified, the default value of label is used.
Example
Recreating the simple report from the previous example, we select the Function option instead of the Static option. This results in a formula being inserted into the spreadsheet, instead of static data. There is a brief pause while communicating with the server, then the retrieved data is displayed.
The formula generated using this example is:
=HISTORIAN.SINGLEVALUE(Sheet1!A2:A4, "interpolated", NOW(), "DateTime", FALSE, "numeric")
You can modify the formula, and the results refresh automatically to reflect your changes. You can also use the function independently from the add-in. For example, the following formula is entered into cell E8:
=HISTORIAN.SINGLEVALUE(Sheet1!A2:A4, "interpolated", NOW(), "DateTime,OPCQuality", FALSE, "numeric")
The resulting report is identical to the first, but with the additional TagName and OPCQuality properties added.
If your version of Excel does not support dynamic arrays, a message similar to the following displays:
In this case, although you can still use the custom function, the output cannot be refreshed automatically. You must manually trigger the update by selecting the output range, and pressing Ctrl-Shift-Enter.