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

Name

Description

CellRange

Cell range to treat like a table. Its first row and its first column are used to reference table cells.

Value

A value to set in referenced table cell.

RowIndex

Optional. 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.

ColumnIndex

Optional. 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.

PushRowAt

Optional. 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.

PushColumnAt

Optional. 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.

AggregationMethod

Optional. One of the predefined numbers which specifies the aggregation method to use for specified table cell. Defaults to 0.

Aggregation Methods

Number

Method

Description

0

NONE

No aggregation is performed and the last valid value 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 recieved values.

8

STDEV.P

Currently not available!!

9

SUM

Calculates the sum of all received values.

Return Value

TRUE or an error value.

Example Below examples assumes following cells in range A3:D5

Time

Turbine 1

Turbine 2

Turbine 3

93370

1000

2000

3000

93360

1500

2500

3500

Function

Result

Description

=TABLE.UPDATE(A3:D5, 42, 93360, “Turbine 1”)

TRUE

Sets value in table cell (93360, “Turbine 1”) to 42

=TABLE.UPDATE(A3:D5, 42, 93300, “Turbine 42”, 1, 1)

TRUE

Inserts 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.