Skip to main content

TABLE.UPDATE

Creates and updates a defined cell range in a table like manner. The first row and first column of specifeid cell range defines the indices used to reference a table cell. To set or update a cell its corresponding row and column indices must be given. If a row index does not exist in current range, it can be automatically added to top or bottom. The same applies for a column index which can be automatically added to the left or right.

To simply read values from a table cell refer to TABLE.GET

Syntax

=TABLE.UPDATE(CellRange, Value [, RowIndex, ColumnIndex, PushRowAt, PushColumnAt, AggregationMethod])

Arguments

NameDescription
CellRangeCell range to treat like a table. Its first row and its first column are used to reference table cells.
ValueA value to set in referenced table cell.
RowIndexOptional. The row index of a table cell. If not defined no value is set. The PushRowAt parameter can be used to add the row index if the table has no equal index.
ColumnIndexOptional. The column index of a table cell. If not defined no value is set. The PushColumnAt parameter can be used to add the column index if the table has no equal index.
PushRowAtOptional. Defines where to insert a new row index. Should be either 1, -1, or 0. To insert new row at the bottom specfiy 1, -1 will insert it at the top and 0 will not insert at all. Defaults to 0.
PushColumnAtOptional. Defines where to insert a new column index. Should be either 1, -1, or 0. To insert new column to the right specfiy 1, -1 will insert it to the left and 0 will not insert at all. Defaults to 0.
AggregationMethodOptional. One of the predefined numbers which specifies the aggregation method to use for specified table cell. Defaults to 0.

Aggregation Methods

NumberMethodDescription
0NONENo aggregation is performed and the last valid value 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.

Return Value

TRUE or an error value.

Example Below examples assumes following cells in range A3:D5

TimeTurbine 1Turbine 2Turbine 3
93370100020003000
93360150025003500
FunctionResultDescription
=TABLE.UPDATE(A3:D5, 42, 93360, "Turbine 1")TRUESets value in table cell (93360, "Turbine 1") to 42
=TABLE.UPDATE(A3:D5, 42, 93300, "Turbine 42", 1, 1)TRUEInserts new row and column indices to the bottom and right of the table and sets the corresponding table cell to 42. Note that the row with index 93370 and the column with index "Turbine 1" are moved out of the table.