TIMEAGGREGATE
Aggregates number values over a specified time period. TIMEAGGREGATE saves the aggregate values in a cell and/or in a TargetRange and returns the last aggregated value. This function only stores data in memory. As soon as the app stops and restarts again the data collection starts over. To store data persistently use a Stream function connecting to a data base (e.g. TimescaleDB).
Syntax
=TIMEAGGREGATE(Number, [Period], [AggregationMethod], [TimeSerial], [Interval], [TargetRange], [Sort], [Limit], [LimitPeriod])
Arguments
| Name | Type | Description |
|---|---|---|
| Number | Number | Number to aggregate. All other values are ignored. |
| Period (optional) | Number | The time period in seconds in which data is collected. ´ Default value: 60 |
| AggregationMethod (optional) | AggregationMethod | One of the predefined numbers which specifies the aggregation method to use. Set aggregation method to NONE to collect the raw values. Options: 0: NONE - No aggregation is performed and the last valid value of specified DataCell is returned. 1: AVERAGE - Calculates the average of all received values. 2: COUNT - Counts the number of received values. 3: COUNTA - Counts all values which are not zero. 4: MAX - Determines the maximum of all received values. 5: MIN - Determines the minimum of all received values. 6: PRODUCT - Calculates the product of all received values. 7: STDEV.S - Calculates the standard deviation of all received values. 8: STDEV.P - Currently not available! 9: SUM - Calculates the sum of all received values. Default value: 0 |
| TimeSerial (optional) | Number | A serial number to use as key for each aggregated value. Default value: "Serial Number for now" |
| Interval (optional) | Number | An interval in seconds at which values should be aggregated. To not loose any data an Interval should always be a divisor of the Period. If not specified values are aggregated with each step. |
| TargetRange (optional) | Range | A cell range to write the aggregated values to. Collects aggregation intervals and displays them. Differs if interval is left blank (see interval). |
| Sort (optional) | Boolean | Set to TRUE if values should be sorted by time. Most useful if a custom TimeSerial parameter is provided. Default value: FALSE |
| Limit (optional) | Number | Specifies the maximum number of values stored. If limit is reached the function returns a #LIMIT error. Default value: 1000 |
| LimitPeriod (optional) | Number | A time period in seconds in which aggregated results are collected. This is an additional limit for the amount of stored results. Specifying a negative value disables this behaviour. Default value: "Same as Period parameter" |
Return
| Type | Description |
|---|---|
| Number | A number value aggregated over all values within specified period. |
Examples
| Formula | Result | Comment |
|---|---|---|
| Calculates the sum of all values read from C2 over a period of 20 seconds. | |
| Calculates the average each 2 seconds and writes roughly 10 values to target range D4:E14 |
The image shows all methods in action. Eight TIMEAGGREGATE functions watch over the values from either the parameter "Berlin", "Paris" or "London". E.g. "TIMEAGGREGATE(B6,,1)*