Ask Your Question

Revision history [back]

click to hide/show revision 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.

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.

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.

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.

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.

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 Constant Inline Array. 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.

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

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:) (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).

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

(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).

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