Additionally, the BFPut<\/a> function is supplied for use in Excel macros, which allows you to submit values to IFS OI Explorer.<\/p>\n You can build a formula in two ways:<\/p>\n Automatically<\/strong>:\u00a0Drag the function from the task pane and drop it onto the Excel\u00ae worksheet, filling in the parameters as required.<\/p>\n Manually<\/strong>:\u00a0Define a formula manually in the spreadsheet. There are four functions that can be defined manually.<\/p>\n <\/p>\n This topic describes additional considerations when using the IFS OI Excel Add-in and the various error conditions that can result from the formulas which you specify.<\/p>\n The display of the retrieved data is restricted to the available rows and columns of the spreadsheet, starting from the specified start cell <\/strong>position.<\/p>\n This limit is known as the Truncation Limit<\/strong>. If a data fetch produces more data points than the truncation limit, a message box appears indicating that data has been truncated.<\/p>\n Any data points beyond the truncation limit will not be returned to the worksheet.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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<\/strong> 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.<\/p>\n Since the data is retrieved via IFS OI 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).<\/p>\n However, since the data is coming from IFS OI Explorer, what is returned may be dependent on more than the function parameters.<\/p>\n 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<\/strong>. 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.<\/p>\n If an error occurs while fetching data for a IFS OI Excel Add-in function, then all of the returned values will be set to #N\/A<\/strong>, even though some of the data may have been fetched successfully. A message box also appears indicating the nature of the error that occurred.<\/p>\n <\/p>\n It is important to note that these fetch functions are array formulas<\/strong>. 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.<\/p>\n The Functions The functions available from this panel are:<\/p>\n Note: BFPut() does not return a value and can only be accessed from VBA code.<\/p>\n To insert the formulas into a spreadsheet:<\/p>\n 1. Select a cell in the Excel spreadsheet, this would usually be cell A1.<\/p>\n 2. Drag and drop the formula onto the cell.<\/p>\n The format of the formula appears in the formula bar.<\/p>\n 3. In the Excel formula bar, replace the tokens with the required information (the format of these functions are described separately).<\/p>\n For example:<\/p>\n The above function describes a call to the BFGetAverageEntityData<\/a> function of the IFS OI 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.<\/p>\n 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).<\/p>\n 5. In the Excel formula bar, click at the end of the formula and then press the Ctrl+Shift+Enter<\/strong> key combination. This is the Excel command to identify that the function is an array formula.<\/p>\n 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:<\/p>\n The following screenshot shows some example results of the BFGetAverageEntityData<\/a>() formula:<\/p>\n <\/p>\n IFS OI Excel Add-in follows standard Excel\u00ae behaviour with regard to array formulas, which means you cannot change part of an array.<\/p>\n To delete an array from a worksheet:<\/p>\n <\/p>\n Format: BFGetAverageEntityData(StartTime, EndTime, Interval, ConsolidationPeriod*, ConsolidationMethod*, TimeBase*<\/em>, Entities, Options)<\/strong><\/p>\n * These parameters are reserved for future use.<\/em><\/p>\n Return Value<\/strong>:\u00a0Returns an array, which includes text (headings and timestamps) and numbers.<\/p>\n BFGetAverageEntityData retrieves averaged entity data from IFS OI Explorer.<\/p>\n 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.<\/p>\n The BFGetAverageEntityData function is an array formula, which must be manually inserted into an Excel\u00ae spreadsheet.<\/p>\n For a step by step example of how to call the function in Excel, see Adding the Fetch Functions<\/a>.<\/p>\n Start Time<\/strong>:\u00a0Retrieve the data recorded on the specified Start Time or after.<\/p>\n End Time<\/strong>:\u00a0Retrieve the data recorded on the specified End Time or before.<\/p>\n The Start Time and End Time can be defined either in ISO 8601 format or in Excel format.<\/p>\n The ISO 8601format accepted in IFS OI Excel Add-in is YYYY-MM-DDThh:mm:ss<\/strong>. Where:<\/p>\n\n
\nUsage Considerations and Error Conditions<\/h2>\n
Fetch Limits<\/h3>\n
Less Data than Allocated Space<\/h3>\n
More Data than Allocated Space<\/h3>\n
Formula Recalculation<\/h3>\n
Fetch Errors<\/h3>\n
\nAdding the Fetch Functions<\/h2>\n
![](\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2020\/02\/word-image-40.png\")
panel assists you with entering an Array Formula Function into an Excel\u00ae 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.<\/p>\n\n
![](\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2020\/02\/word-image-41.png\")
<\/p>\n=BFGetAverageEntityData(\"2016-06-01 01:00:00\", \"2016-06-15 01:00:00\", 30, \"\", \"\", \"\", \"CADMIUM\", \"properties=off,confidence=on\")<\/code><\/p>\n\n
![](\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2020\/02\/word-image-42.png\")
<\/p>\n
\nChanging or Deleting an Array<\/h2>\n
\n
\nBFGetAverageEntityData<\/h2>\n