Home Server Excel Add-in Array Formula Functions

Array Formula Functions

The P2 Excel Add-in Functions can be used in the Microsoft® Excel® formulas to refine your calculations. There are seven array formula functions:

Additionally, the BFPut function is supplied for use in Excel macros, which allows you to submit values to P2 Explorer.

You can build a formula in two ways:

Automatically: Drag the function from the task pane and drop it onto the Excel® worksheet, filling in the parameters as required.

Manually: Define a formula manually in the spreadsheet. There are four functions that can be defined manually.

 

Usage Considerations and Error Conditions

This topic describes additional considerations when using the P2 Excel Add-in and the various error conditions that can result from the formulas which you specify.

Fetch Limits

The display of the retrieved data is restricted to the available rows and columns of the spreadsheet, starting from the specified start cell position.

This limit is known as the Truncation Limit. If a data fetch produces more data points than the truncation limit, a message box appears indicating that data has been truncated.

Any data points beyond the truncation limit will not be returned to the worksheet.

Less Data than Allocated Space

An array formula in Excel covers a defined area of cells. If the function returns less columns or rows than the allocated space, the spare cells will be set to empty.

More Data than Allocated Space

If the parameters for a function are not fixed (for example, when defining a Start Time and End Time range which covers the future) then the number of data points returned by the function may change between calculations.

It is therefore possible that a function will return more data than was allocated for it by the array formula inserted by the Fetch Wizards, or inserted manually. In this case, the function is not able to automatically resize the array formula to which it is assigned.

The function can, however, discover the size of the area of cells which have been allocated to it, and therefore can determine if it has enough allocated space.

If the function requires more space than is allocated, a message box appears, indicating the size allocated and the size actually required. The function will also return #N/A for all the cell values, even though some of the fetched data may be able to fit, to clarify that the data may not be valid. In this case, the array formula which uses the function needs to be resized, to make it large enough to hold the returned data.

Formula Recalculation

Since the data is retrieved via P2 Excel Add-in functions, it is automatically refreshed by Excel's calculation engine whenever a cell that the function is dependent on changes (if automatic calculation is turned on).

However, since the data is coming from P2 Explorer, what is returned may be dependent on more than the function parameters.

For example, if the Start Time or End Time range covers the future (meaning new data points will be added as they are recorded), you can force a recalculation by pressing F9. When forced to recalculate, it initiates a new fetch of data. This will recalculate all formulas in all worksheets of the current workbook. This is Excel's default behaviour.

Fetch Errors

If an error occurs while fetching data for a P2 Excel Add-in function, then all of the returned values will be set to #N/A, even though some of the data may have been fetched successfully. A message box also appears indicating the nature of the error that occurred.

 

Adding the Fetch Functions

It is important to note that these fetch functions are array formulas. Array formulas expect you to select the exact number of cells (rows and columns) that will carry the result of the formula. Since this is often not known until after the calculation is performed, there is a special method of inserting the formula into an Excel spreadsheet.

The Functions panel assists you with entering an Array Formula Function into an Excel® spreadsheet. Although you could manually type in the formula, using the Functions panel helps you with the correct syntax and parameters required for each function.

The functions available from this panel are:

  • Average: BFGetAverageEntityData
  • Raw: BFGetRawEntityData
  • Last Known: BFGetLastKnownValueEntityData
  • Interpolate: BFGetLinearInterpolateEntityData
  • BabelFish Time: BFTimeFromExcelTime
  • Raw With Interval: BFGetRawEntityDataEx

Note: BFPut() does not return a value and can only be accessed from VBA code.

To insert the formulas into a spreadsheet:

1. Select a cell in the Excel spreadsheet, this would usually be cell A1.

2. Drag and drop the formula onto the cell.

The format of the formula appears in the formula bar.

3. In the Excel formula bar, replace the tokens with the required information (the format of these functions are described separately).

For example:

=BFGetAverageEntityData("2016-06-01 01:00:00", "2016-06-15 01:00:00", 30, "", "", "", "CADMIUM", "properties=off,confidence=on")

The above function describes a call to the BFGetAverageEntityData function of the P2 Excel Add-in. This function fetches the average sample type results for the entity "CADMIUM" from 1am on 1st June 2016 to 1am on 15th June 2016, and with an interval of 30 seconds.

4. Select the number of rows and columns required to hold the results of the function, including the first cell you added the formula in (e.g. in this example you would select 3 columns and 34 rows to contain the results).

5. In the Excel formula bar, click at the end of the formula and then press the Ctrl+Shift+Enter key combination. This is the Excel command to identify that the function is an array formula.

6. The first column of the results now needs to be formatted as Date/Time type data, along with any other relevant cells containing date/time. To do this:

  1. Select the first column of the results.
  2. Right-click on the column header and select Format Cells.
  3. In the Number tab, click the Custom category and select the Date/Time format required. You can also enter a custom format string, such as yyyy-mm-dd hh:mm:ss.

The following screenshot shows some example results of the BFGetAverageEntityData() formula:

 

Changing or Deleting an Array

P2 Excel Add-in follows standard Excel® behaviour with regard to array formulas, which means you cannot change part of an array.

To delete an array from a worksheet:

  1. Select the entire array.
  2. Press the Delete key.

 

BFGetAverageEntityData

Format: BFGetAverageEntityData(StartTime, EndTime, Interval, ConsolidationPeriod*, ConsolidationMethod*, TimeBase*, Entities, Options)

* These parameters are reserved for future use.

Return Value: Returns an array, which includes text (headings and timestamps) and numbers.

BFGetAverageEntityData retrieves averaged entity data from P2 Explorer.

For a specified list of entities, it retrieves the averaged value of the data points starting at a specified Start Time, and at a specified Interval (number of seconds) until the End Time is reached.

The BFGetAverageEntityData function is an array formula, which must be manually inserted into an Excel® spreadsheet.

For a step by step example of how to call the function in Excel, see Adding the Fetch Functions.

Start Time: Retrieve the data recorded on the specified Start Time or after.

End Time: Retrieve the data recorded on the specified End Time or before.

The Start Time and End Time can be defined either in ISO 8601 format or in Excel format.

The ISO 8601format accepted in P2 Excel Add-in is YYYY-MM-DDThh:mm:ss. Where:

YYYY

MM

DD

hh

mm

ss

Four-digit year

Month (01 to 12)

Two-digit day of month (01 through 31)

Hour in 24 hour format (00 - 23)

Minutes (00 to 59)

Seconds (00 through 59)

If the time is specified in Excel format, then the BFTimeFromExcelTime function must be used to convert from Excel format to ISO8601 format. This is so that you can easily use Excel formulas to calculate a time, then convert that time for use with the P2 Explorer functions.

The Start Time and End Time can also be specified in Normalised Time through the use of keywords and offsets. For example, NOW - 2d.

Interval: The number of seconds between each data point retrieved. For example, with a start time of 2009-01-28T8:00:00 and an interval of 300 seconds (5 minutes), data points will be retrieved at 2009-01-28T8:00:00 and 5 minutes later at 2009-01-28T8:05:00, and so on. If the end time is an exact integer multiple of intervals after the start time, then the last value retrieved will be at the end time. Otherwise, the last value retrieved will be before the end time. For example, if the end time is 2009-01-28T8:10:00 then values will be retrieved at 8:00, 8:05 and 8: 10 (the end time). If the end time is 2009-01-28T8:12:00 then the same values will be retrieved.

Consolidation Period: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Consolidation Method: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Time Base: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Entities: A string containing a semicolon-separated list of entity names. For example, "OICI001.PV;OICI002.PV;OICI003.PV". Alternatively, you can specify the entity names using the cell references. For example, if one entity name is in cell A2 and another is in cell A3, then the formula can be entered with 'A2 & ";" & A3' as the entity list (in Excel, an ampersand (&) performs string concatenation).

Options: A semicolon-separated list of option=value pairs, specifying various options. Currently, only the properties option is supported by P2 Excel Add-in functions. When the properties parameter is turned on, then P2 Excel Add-in retrieves and displays the Entity Name, Description, Type, Measurement Type, Units, and the Location properties of the retrieved entities. The possible values for the properties option are:

properties=on

properties=yes

properties=1

properties=off

properties=no

properties=0

Display the properties of the entities.

Display the properties of the entities.

Display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

 

BFGetRawEntityData

Format: BFGetRawEntityData(StartTime, EndTime, TimeBase*, Entities, Options)

* These parameters are reserved for future use.

Return Value: Returns an array, which includes text (headings and timestamps) and numbers.

BFGetRawEntityData retrieves raw entity data from P2 Explorer.

For a specified list of entities, it retrieves all raw data points recorded between a specified Start Time and End Time.

The BFGetRawEntityData function is an array formula, which must be manually inserted into an Excel® spreadsheet.

For a step by step example of how to call the function in Excel, see Adding the Fetch Functions.

Start Time: Retrieve the data recorded on the specified Start Time or after.

End Time: Retrieve the data recorded on the specified End Time or before.

The Start Time and End Time can be defined either in ISO 8601 format or in Excel format.

The ISO 8601format accepted in P2 Excel Add-in is YYYY-MM-DDThh:mm:ss. Where:

YYYY

MM

DD

hh

mm

ss

Four-digit year

Month (01 to 12)

Two-digit day of month (01 through 31)

Hour in 24 hour format (00 - 23)

Minutes (00 to 59)

Seconds (00 through 59)

If the time is specified in Excel format, then the BFTimeFromExcelTime function must be used to convert from Excel format to ISO8601 format. This is so that you can easily use Excel formulas to calculate a time, then convert that time for use with the P2 Explorer functions.

The Start Time and End Time can also be specified in Normalised Time through the use of keywords and offsets. For example, NOW - 2d.

Time Base: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Entities: A string containing a semicolon-separated list of entity names. For example, "OICI001.PV;OICI002.PV;OICI003.PV". Alternatively, you can specify the entity names using the cell references. For example, if one entity name is in cell A2 and another is in cell A3, then the formula can be entered with 'A2 & ";" & A3' as the entity list (in Excel, an ampersand (&) performs string concatenation).

Options: A semicolon-separated list of option=value pairs, specifying various options. Currently, only the properties option is supported by P2 Excel Add-in functions. When the properties parameter is turned on, then P2 Excel Add-in retrieves and displays the Entity Name, Description, Type, Measurement Type, Units, and the Location properties of the retrieved entities. The possible values for the properties option are:

properties=on

properties=yes

properties=1

properties=off

properties=no

properties=0

Display the properties of the entities.

Display the properties of the entities.

Display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

 

BFGetRawEntityDataEx

Format: BFGetRawEntityDataEx(StartTime, EndTime, Interval, TimeBase*, Entities, Options)

* These parameters are reserved for future use.

Return Value: Returns an array, which includes text (headings and timestamps) and numbers.

BFGetRawEntityDataEx retrieves raw entity data from P2 Explorer.

For a specified list of entities, it retrieves all raw data points recorded between a specified Start Time and End Time.

The BFGetRawEntityDataEx function is an array formula, which must be manually inserted into an Excel® spreadsheet.

For a step by step example of how to call the function in Excel, see Adding the Fetch Functions..

Start Time: Retrieve the data recorded on the specified Start Time or after.

End Time: Retrieve the data recorded on the specified End Time or before.

The Start Time and End Time can be defined either in ISO 8601 format or in Excel format.

The ISO 8601format accepted in P2 Excel Add-in is YYYY-MM-DDThh:mm:ss. Where:

YYYY

MM

DD

hh

mm

ss

Four-digit year

Month (01 to 12)

Two-digit day of month (01 through 31)

Hour in 24 hour format (00 - 23)

Minutes (00 to 59)

Seconds (00 through 59)

If the time is specified in Excel format, then the BFTimeFromExcelTime function must be used to convert from Excel format to ISO8601 format. This is so that you can easily use Excel formulas to calculate a time, then convert that time for use with the P2 Explorer functions.

The Start Time and End Time can also be specified in Normalised Time through the use of keywords and offsets. For example, NOW - 2d.

Interval: The number of seconds between each data point retrieved. For example, with a start time of 2009-01-28T8:00:00 and an interval of 300 seconds (5 minutes), data points will be retrieved at 2009-01-28T8:00:00 and 5 minutes later at 2009-01-28T8:05:00, and so on. If the end time is an exact integer multiple of intervals after the start time, then the last value retrieved will be at the end time. Otherwise, the last value retrieved will be before the end time. For example, if the end time is 2009-01-28T8:10:00 then values will be retrieved at 8:00, 8:05 and 8: 10 (the end time). If the end time is 2009-01-28T8:12:00 then the same values will be retrieved. Note: Only calculation tags will make use of the interval on raw data request.

Time Base: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Entities: A string containing a semicolon-separated list of entity names. For example, "OICI001.PV;OICI002.PV;OICI003.PV". Alternatively, you can specify the entity names using the cell references. For example, if one entity name is in cell A2 and another is in cell A3, then the formula can be entered with 'A2 & ";" & A3' as the entity list (in Excel, an ampersand (&) performs string concatenation).

Options: A semicolon-separated list of option=value pairs, specifying various options. Currently, only the properties option is supported by P2 Excel Add-in functions. When the properties parameter is turned on, then P2 Excel Add-in retrieves and displays the Entity Name, Description, Type, Measurement Type, Units, and the Location properties of the retrieved entities. The possible values for the properties option are:

properties=on

properties=yes

properties=1

properties=off

properties=no

properties=0

Display the properties of the entities.

Display the properties of the entities.

Display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

 

BFGetLastKnownValueEntityData

Format: BFGetLastKnownValueEntityData(StartTime, EndTime, Interval, ConsolidationPeriod*, ConsolidationMethod*, TimeBase*, Entities, Options)

* These parameters are reserved for future use.

Return Value: Returns an array, which includes text (headings and timestamps) and numbers.

BFGetLastKnownValueEntityData retrieves a snapshot of entity data from P2 Explorer.

For a specified list of entities, the function retrieves snapshot data points starting at a specified Start Time, and at a specified Interval (number of seconds), until the End Time is reached. At each time, the value of the snapshot is the previously recorded raw data point.

The BFGetLastKnownValueEntityData function is an array formula, which must be manually inserted into an Excel® spreadsheet.

For a step by step example of how to call the function in Excel, see Adding the Fetch Functions.

Start Time: Retrieve the data recorded on the specified Start Time or after.

End Time: Retrieve the data recorded on the specified End Time or before.

The Start Time and End Time can be defined either in ISO 8601 format or in Excel format.

The ISO 8601format accepted in P2 Excel Add-in is YYYY-MM-DDThh:mm:ss. Where:

YYYY

MM

DD

hh

mm

ss

Four-digit year

Month (01 to 12)

Two-digit day of month (01 through 31)

Hour in 24 hour format (00 - 23)

Minutes (00 to 59)

Seconds (00 through 59)

If the time is specified in Excel format, then the BFTimeFromExcelTime function must be used to convert from Excel format to ISO8601 format. This is so that you can easily use Excel formulas to calculate a time, then convert that time for use with the P2 Explorer functions.

The Start Time and End Time can also be specified in Normalised Time through the use of keywords and offsets. For example, NOW - 2d.

Interval: The number of seconds between each data point retrieved. For example, with a start time of 2009-01-28T8:00:00 and an interval of 300 seconds (5 minutes), data points will be retrieved at 2009-01-28T8:00:00 and 5 minutes later at 2009-01-28T8:05:00, and so on. If the end time is an exact integer multiple of intervals after the start time, then the last value retrieved will be at the end time. Otherwise, the last value retrieved will be before the end time. For example, if the end time is 2009-01-28T8:10:00 then values will be retrieved at 8:00, 8:05 and 8: 10 (the end time). If the end time is 2009-01-28T8:12:00 then the same values will be retrieved.

Consolidation Period: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Consolidation Method: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Time Base: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Entities: A string containing a semicolon-separated list of entity names. For example, "OICI001.PV;OICI002.PV;OICI003.PV". Alternatively, you can specify the entity names using the cell references. For example, if one entity name is in cell A2 and another is in cell A3, then the formula can be entered with 'A2 & ";" & A3' as the entity list (in Excel, an ampersand (&) performs string concatenation).

Options: A semicolon-separated list of option=value pairs, specifying various options. Currently, only the properties option is supported by P2 Excel Add-in functions. When the properties parameter is turned on, then P2 Excel Add-in retrieves and displays the Entity Name, Description, Type, Measurement Type, Units, and the Location properties of the retrieved entities. The possible values for the properties option are:

properties=on

properties=yes

properties=1

properties=off

properties=no

properties=0

Display the properties of the entities.

Display the properties of the entities.

Display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

 

BFGetLinearInterpolateEntityData

Format: BFGetLinearInterpolateEntityData(StartTime, EndTime, Interval, ConsolidationPeriod*, ConsolidationMethod*, TimeBase*, Entities, Options)

* These parameters are reserved for future use.

Return Value: Returns an array, which includes text (headings and timestamps) and numbers.

BFGetLinearInterpolateEntityData retrieves interpolated entity data from P2 Explorer.

For a specified list of entities, the function retrieves interpolated data points starting at a specified Start Time and at a specified Interval (number of seconds), until the End Time is reached. At each time the value retrieved is interpolated between the raw data points surrounding it.

The difference between this function and the BFGetLastKnownValueEntityData function is that the returned values are interpolated, meaning they will generally be different to the raw values. The Confidence value returned for the interpolated points relates to how close the point is to the raw data point. The closer it is, the more likely that the result is accurate.

The BFGetLinearInterpolateEntityData function is an array formula, which must be manually inserted into an Excel® spreadsheet.

For a step by step example of how to call the function in Excel, see Adding the Fetch Functions.

Start Time: Retrieve the data recorded on the specified Start Time or after.

End Time: Retrieve the data recorded on the specified End Time or before.

The Start Time and End Time can be defined either in ISO 8601 format or in Excel format.

The ISO 8601format accepted in P2 Excel Add-in is YYYY-MM-DDThh:mm:ss. Where:

YYYY

MM

DD

hh

mm

ss

Four-digit year

Month (01 to 12)

Two-digit day of month (01 through 31)

Hour in 24 hour format (00 - 23)

Minutes (00 to 59)

Seconds (00 through 59)

If the time is specified in Excel format, then the BFTimeFromExcelTime function must be used to convert from Excel format to ISO8601 format. This is so that you can easily use Excel formulas to calculate a time, then convert that time for use with the P2 Explorer functions.

The Start Time and End Time can also be specified in Normalised Time through the use of keywords and offsets. For example, NOW - 2d.

Interval: The number of seconds between each data point retrieved. For example, with a start time of 2009-01-28T8:00:00 and an interval of 300 seconds (5 minutes), data points will be retrieved at 2009-01-28T8:00:00 and 5 minutes later at 2009-01-28T8:05:00, and so on. If the end time is an exact integer multiple of intervals after the start time, then the last value retrieved will be at the end time. Otherwise, the last value retrieved will be before the end time. For example, if the end time is 2009-01-28T8:10:00 then values will be retrieved at 8:00, 8:05 and 8: 10 (the end time). If the end time is 2009-01-28T8:12:00 then the same values will be retrieved.

Consolidation Period: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Consolidation Method: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Time Base: This parameter is reserved for future use. Therefore, an empty string ("") must be supplied.

Entities: A string containing a semicolon-separated list of entity names. For example, "OICI001.PV;OICI002.PV;OICI003.PV". Alternatively, you can specify the entity names using the cell references. For example, if one entity name is in cell A2 and another is in cell A3, then the formula can be entered with 'A2 & ";" & A3' as the entity list (in Excel, an ampersand (&) performs string concatenation).

Options: A semicolon-separated list of option=value pairs, specifying various options. Currently, only the properties option is supported by P2 Excel Add-in functions. When the properties parameter is turned on, then P2 Excel Add-in retrieves and displays the Entity Name, Description, Type, Measurement Type, Units, and the Location properties of the retrieved entities. The possible values for the properties option are:

properties=on

properties=yes

properties=1

properties=off

properties=no

properties=0

Display the properties of the entities.

Display the properties of the entities.

Display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

Do not display the properties of the entities.

 

BFTimeFromExcelTime

Format: BFTimeFromExcelTime(TimeString)

Return Value: Returns a string containing the time in ISO 8601 format.

BFTimeFromExcelTime converts an Excel® time into a string in ISO-8601 format (without a time zone) which can be passed to one of the P2 Explorer fetch data functions (such as BFGetAverageEntityData) as a Start Time or End Time.

TimeString: The fixed time string entered in Excel format, or the cell reference that contains a Excel time data value.

Example 1

=BFTimeFromExcelTime("12/08/2003 12:00:00")

Example 2

=BFTimeFromExcelTime($A$1)

 

Comments are closed