Skip to main content
Version: Streamsheets 2.5

TIMEAGGREGATE

Aggregates number values over a specified time period. TIMEAGGREGATE saves the raw values in the cell and/or in a TargetRange. The returned value in the cell is aggregated over all numbers within the period. This function only stores data in memory. As soon as the app stops and restarts again the data collection starts over. To store data persistent use a Stream function connecting to a data base (e.g. TimescaleDB).

Syntax

=TIMEAGGREGATE(DataCell, [Period], [AggregationMethod], [TimeSerial], [Interval], [TargetRange], [Sort], [Limit])

Arguments

NameDescription
DataCellThe data source cell which should evaluate to a number. All other values are ignored.
Period (optional)The time period in seconds in which data is collected. Defaults to 60 seconds.
AggregationMethod (optional)One of the predefined numbers which specifies the aggregation method to use. Defaults to 0.

NumberMethodDescription
0NONENo aggregation is performed and the last valid value of specified DataCell is returned.
1AVERAGECalculates the average of all received values.
2COUNTCounts the number of received values.
3COUNTACounts all values which are not zero.
4MAXDetermines the maximum of all received values.
5MINDetermines the minimum of all received values.
6PRODUCTCalculates the product of all received values.
7STDEV.SCalculates the standard deviation of all recieved values.
8STDEV.PCurrently not available!!
9SUMCalculates the sum of all received values.
TimeSerial (optional)A serial number to use as key for each aggregated value. Defaults to now.
Interval (optional)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. Note: no default value is set for an Interval. If it is not specified no aggregation is done and values are simply collected until limit is reached.
TargetRange (optional)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)Set to TRUE if values should be sorted by time. Most useful if a custom TimeSerial parameter is provided. Defaults to FALSE.
Limit (optional)Specifies the maximum number of values stored. If limit is reached the function returns a #LIMIT error. Defaults to 1000.

Return

A number value aggregated over all values within specified period.

Examples

FormulaResultComment
=TIMEAGGREGATE(C2, 20)
Calculates the sum of all values read from C2 over a period of 20 seconds.
=TIMEAGGREGATE(C2, 20, 1,,2, D4:E14)
Calculates the average each 2 seconds and writes roughly 10 values to target range D4:E14

TA

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)*