First time here? Check out the FAQ!

1 | initial version |

Cells can have three content types: Number, Text, Formula

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result
There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself.

With `A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

will work as expected and return the result =AVERAGE(A1:A3) also would.

Not as the content of a cell but as a sub-expression in a formula you may also use the type `Constant Inline Array`

. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

2 | No.2 Revision |

Cells can have three content types: Number, Text, Formula

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula ~~result ~~result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. ~~ ~~Another way to reference a range (vector or 2D-array) - exemplified for A1:A3 again - is `=OFFSET(A1;0;0;3;1)`

.

With `A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

will work as expected and return the result =AVERAGE(A1:A3) also would.

Not as the content of a cell but as a sub-expression in a formula you may also use the type `Constant Inline Array`

. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

3 | No.3 Revision |

Cells can have three content types: Number, Text, Formula

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. Another way to reference a range (vector or 2D-array) - exemplified for ~~A1:A3 ~~the above range again - is

.~~=OFFSET(A1;0;0;3;1)~~=OFFSET(MySheet.A1;0;0;3;1)

With `A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

or `=AVERAGE(OFFSET(A1;0;0;3;1))`

will work as expected and return the result =AVERAGE(A1:A3) also would.

Not as the content of a cell but as a sub-expression in a formula you may also use the type `Constant Inline Array`

. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

4 | No.4 Revision |

Cells can have three content types: Number, Text, Formula

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. Another way to reference a range (vector or 2D-array) - exemplified for the above range again - is `=OFFSET(MySheet.A1;0;0;3;1)`

.

With `A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

or `=AVERAGE(OFFSET(A1;0;0;3;1))`

will work as expected and return the result ~~=AVERAGE(A1:A3) ~~`=AVERAGE(A1:A3)`

also would.

`Constant Inline Array`

. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

5 | No.5 Revision |

Cells can have three content types: Number, Text, Formula

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. Another way to reference a range (vector or 2D-array) - exemplified for the above range again - is `=OFFSET(MySheet.A1;0;0;3;1)`

.

With `A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

or `=AVERAGE(OFFSET(A1;0;0;3;1))`

will work as expected and return the result `=AVERAGE(A1:A3)`

also would.

Not as the content of a cell but as a sub-expression in a formula you may also use the type `Constant `

. Array. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like **Constant** Inline ~~Array~~`{3,5,17}`

(Regard the **curly** brackets!) e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

6 | No.6 Revision |

Cells can have three content types: Number, Text, Formula

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. Another way to reference a range (vector or 2D-array) - exemplified for the above range again - is `=OFFSET(MySheet.A1;0;0;3;1)`

.

With `A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

or `=AVERAGE(OFFSET(A1;0;0;3;1))`

will work as expected and return the result `=AVERAGE(A1:A3)`

also would.

Not as the content of a cell but as a sub-expression in a formula you may also use the type **Constant** Inline Array. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

(Regard the **curly** brackets!) e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

(Editing:)

Most likely a formula like `=AVERAGE(OFFSET(INDIRECT(C1);0;0;C2;C3))`

would offer all the desirable flexibility: With the address (K5 e.g.) of the cell to start with as text in C1, the number of rows to include (9 e.g.) in C2, and the number of columns (3 e.g.) to include in C3 the above formula will calculate as if it was `=AVERAGE(K5:M13)`

(regarding the given example values).

7 | No.7 Revision |

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. Another way to reference a range (vector or 2D-array) - exemplified for the above range again - is `=OFFSET(MySheet.A1;0;0;3;1)`

.

`A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

or `=AVERAGE(OFFSET(A1;0;0;3;1))`

will work as expected and return the result `=AVERAGE(A1:A3)`

also would.

Not as the content of a cell but as a sub-expression in a formula you may also use the type **Constant** Inline Array. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

(Regard the **curly** brackets!) e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

~~(Editing:) ~~(Editing with respect to the first two comments below:)

Most likely a formula like `=AVERAGE(OFFSET(INDIRECT(C1);0;0;C2;C3))`

would offer all the desirable flexibility: With the address (K5 e.g.) of the cell to start with as text in C1, the number of rows to include (9 e.g.) in C2, and the number of columns (3 e.g.) to include in C3 the above formula will calculate as if it was `=AVERAGE(K5:M13)`

(regarding the given example values).

8 | No.8 Revision |

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. Another way to reference a range (vector or 2D-array) - exemplified for the above range again - is `=OFFSET(MySheet.A1;0;0;3;1)`

.

`A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

or `=AVERAGE(OFFSET(A1;0;0;3;1))`

will work as expected and return the result `=AVERAGE(A1:A3)`

also would.

Not as the content of a cell but as a sub-expression in a formula you may also use the type **Constant** Inline Array. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

(Regard the **curly** brackets!) e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

(Editing with respect to the first two comments below:)

Most likely a formula like `=AVERAGE(OFFSET(INDIRECT(C1);0;0;C2;C3))`

would offer all the desirable flexibility: With the address (K5 e.g.) of the cell to start with as text in C1, the number of rows to include (9 e.g.) in C2, and the number of columns (3 e.g.) to include in C3 the above formula will calculate as if it was `=AVERAGE(K5:M13)`

(regarding the given example ~~values).~~values). See attached example.

9 | No.9 Revision |

(FINAL amendment regarding the comments by the OQ:) See this new attachment!

(Original answer based on misunderstandings due to unclear question:)

Cells can have three content types: Number, Text, Formula

Cells can have two(three) result types: Number, Text (and Error) either from direct entry or as a formula result.

There is neither a content type nor a result type like Vector or Array ...
Among the results (may be direct content, too) of type Text there can also be strings determining a reference like "MySheet.A1:A3" (without the quotes) e.g. In such a case the function INDIRECT applied to the cell will return the reference itself. Another way to reference a range (vector or 2D-array) - exemplified for the above range again - is `=OFFSET(MySheet.A1;0;0;3;1)`

.

`A1:A3`

in cell `B1`

of the same sheet, `=AVERAGE(INDIRECT(B1))`

or `=AVERAGE(OFFSET(A1;0;0;3;1))`

will work as expected and return the result `=AVERAGE(A1:A3)`

also would.

**Constant** Inline Array. The precise syntax is depending on the locale and/or your options settings. An as-if-row CIL might look like `{3,5,17}`

(Regard the **curly** brackets!) e.g. in a locale not using the comma as its decimal separator. `=AVERAGE({3,5,17})`

will then return 8.33... CIL are not accepted by INDIRECT. They rarely are of much use.

(Editing with respect to the first two comments below:)

Most likely a formula like `=AVERAGE(OFFSET(INDIRECT(C1);0;0;C2;C3))`

would offer all the desirable flexibility: With the address (K5 e.g.) of the cell to start with as text in C1, the number of rows to include (9 e.g.) in C2, and the number of columns (3 e.g.) to include in C3 the above formula will calculate as if it was `=AVERAGE(K5:M13)`

(regarding the given example values). See attached example.

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.