Excel Add-In Functions

The dataPARC Excel Add-In is a powerful tool for harnessing the power of dataPARC in a familiar spreadsheet format. In addition to its pre-configured data extraction sheet templates, the dataPARC Excel Add-in also includes a set of cell formula functions that can be inserted into any cell on any sheet. These functions provide maximum flexibility in cases where specific tag values, attributes, statistics, or other calculations are needed without the constraints of tabular-format Normalized Data or Raw Data sheets. They can also be configured to automatically update with current data without the use of VBA macros.

Cell formulas utilizing the dataPARC Excel Add-In functions can be built using the Function Painter tool, eliminating the need to memorize the function names or argument structures. All arguments can be set as cell references, and many can also utilize dataPARC tag references.

Six functions are included:

Function Painter

The fastest way to get started with dataPARC Excel Add-In functions is the Function Painter.

Clicking on the Function Painter in the dataPARC toolbar opens a function configuration pane on the right-side of the screen.

Each tab accesses a function’s configuration. Functions can be built by entering references and selections into the fields, then clicking OK. Tags can be dragged and dropped from the Tag Browser into any tag reference field.

Current Value Function

The Current Value Function is used to retrieve a tag’s value or other attribute at the time of execution. This is useful for ad-hoc report sheets or calculations intended to display or evaluate the current state of a process. If automatic updates are desired, click “Start Timer” in the dataPARC toolbar just to the right of the Function Painter button.

In the Function Painter, enter the desired tag, Update Interval, and choose a tag attribute from the drop-down menu.

Clicking “OK” populates the Excel formula bar with the completed function in the form (note that Attribute is optional; omitting it returns the Value attribute):

=fnPV([uTag], [Interval], [Attribute])

In the example above, the formula is as follows:

=fnPV("VAN.Sample.1-Sine", 60)

Past Value Function

The Past Value Function is used to retrieve a tag’s value or other attribute at a specified point in time. When the desired time is set to a cell reference in Excel, this allows data to be displayed based on a timestamp determined elsewhere in the sheet.

In the Function Painter, enter the following:

• Tag

• Date (timestamp)

• Criterion - determines how the data is retrieved (optional, default = Lin)

  • Lin = linear interpolation between the last value before the Date, and the next value after

  • Exact = value on exactly the specified Data, returns #NODATA if none exists

  • State = most recent value prior to Date

• Return - choose the desired attribute (optional, default = Value)

Clicking “OK” populates the Excel formula bar with the completed function in the form:

=fnAtTimeArray([uTag], [Date], [Criterion], [Attribute])

In the example above, the formula is as follows (this formula returns the tag’s quality at the most recent time prior to the Date):

=fnAtTimeArray("VAN.Sample.312FIC304", "2023-12-26 00:00:00", "State", "Qual")

Tag Statistic Function

The Tag Statistic Function returns the result of a statistical calculation for a tag over a specified time period. This can be used for Excel sheets intended to summarize process data or evaluate variability.

In the Function Painter, enter the following:

• Tag

• Start Date (timestamp)

• End Date (timestamp)

• Statistic - all standard dataPARC statistics are available (optional, default = Avg)

• Return - choose the desired attribute (optional, default = Value)

• Return array of values checkbox - if chosen, an array of cells will be populated, with the current selection as the upper-left cell

  • Include timestamps as first column checkbox

  • Use step size - if chosen, each row of the array will increment by the specified time interval

  • Use fixed number of steps - if chosen, the time span will be split evenly into the specified number of steps to determine the number of array rows

Clicking “OK” populates the Excel formula bar with the completed function in the form:

=fnTagStat([uTag], [Start Time], [End Time], [Statistic], [Return])

In the example above, the formula is as follows (this formula returns the timestamp corresponding to tag’s maximum value during the time period):

=fnTagStat("VAN.Sample.312DIC200", "2023-12-26 10:42:52", "2023-12-27 10:42:52", "Max", "Time")

Time Calculation Function

The Time Calculation Function returns the amount of time a tag’s value meets a certain set of criteria. This is useful when tracking process downtime, total equipment runtime or building totalizer calculations.

In the Function Painter, enter the following:

• Tag

• Start Date (timestamp)

• End Date (timestamp)

• Criteria - Can be set greater or less than a single value, or bracketed within two values. If using a single criteria, it can be set to a tag reference.

• Return Type - select the desired format of result

  • Total duration matching criteria - returns a single value equal to the sum of time for the entire period

  • Duration of each matching interval - returns an array with a single column of duration values for each matching interval over the period

  • Duration and start times - returns an array with two columns; start times and duration values for each matching interval over the period

• Duration Units - select %, seconds, minutes, hours, or days

• Max array rows - specify the maximum number of rows to display if the selected Return Type is an array

Clicking “OK” populates the Excel formula bar with the completed function in the form:

=fnValTime([uTag], [Start Time], [End Time], [First Criteria Value], [Criteria Comparator], [Second Criteria Value], [Duration Units], [Max Array Rows], [Show Timestamps Boolean])

In the example above, the formula is as follows (this formula returns an array of up to 20 start times and durations of intervals when the tag’s value was greater or equal to 1 and less than 3 during the time period):

{=fnValTime("VAN.Sample.312XS905", "2023-12-01 00:00:00", "2023-12-22 00:00:00", 1, "[,)", 3, "H", 20, TRUE)}

Limits Function

The Limits Function returns a tag’s target or limit from the dataPARC database. Limits for specified times and/or grades can be retrieved This is useful when building spreadsheets to track quality compliance or KPI performance.

In the Function Painter, enter the following:

• Tag

• Choose a Grade option

  • Use current grade (or no grade) - retrieves target or limit for current grade or non grade-based limits

  • Use a fixed grade - retrieves target or limit for a specified grade (note, if this grade does not match the grade running at the specified time, the formula will return #NODATA)

• Choose a Time option

  • Get current limit - retrieves the target or limit at the current time

  • Get limit for a specific date - retrieves the target or limit from a previous timestamp (if Use Current Grade is selected above, check “Use grade at this date” to ensure grade/date matching)

• Limit Type - choose Control, Operator, or Spec.

• Select the desired target or limit

Clicking “OK” populates the Excel formula bar with the completed function in the form:

=fnLimit([uTag], [Grade or “AtTime”], [Limit Type], [Desired Target or Limit], [Timestamp])

In the example above, the formula is as follows (this formula returns the tag’s target when running grade “O56PP” at 11:15:30 am on 12/27/2023):

=fnLimit("VAN.PHD.02PM2Bwavg", "O56PP", 1, "TARGET", "2023-12-27 11:15:30")

Steam Function

The Steam Function returns selected physical properties of steam and/or liquid water based on given inputs. Note that a base UTag is not needed, but any of the input arguments can be set as cell references or tag references.

In the Function Painter, enter the following:

• Output - Select the desired output property and units

• Phase - Select the appropriate phase

• Pressure, Temperature, Enthalpy, Entropy, Quality - The available/required entries will vary depending on the chosen output property and phase selection. Units must also be specified.

Clicking “OK” populates the Excel formula bar with the completed function; the function name and arguments vary based on the selected output property, input property, and phase selection.

In the example above, the formula is as follows (this formula returns the enthalpy of saturated steam at the temperature specified by the tag’s value):

=fnSteamTSat("VAN.Sample.312Ti199A", "F", "Enthalpy", "BTU/lb", "VAP")