We're currently migrating from Ask to Discourse, read the details here

Ask Your Question

How to define a range in calc as start cell and number of cells?

asked 2021-01-13 21:40:58 +0200

tpollak gravatar image
A1 contains =sum(B2:B4)
A2 contains =average(B2:B4)
A3 contains =sum(c2:c4)

Column B  Column C
Header      Header
value 1      value 1
value 2      value 2
value 3      value 3
value 4      value 4

To simplify I would like to do something like A4 contains 3

A1 =sum(B2 ?A4)
A2 =average(B ?A4)
A3 =sum (C2 ?A4)

so I easily can change the range by changing the content of A4.

Is something like this possible? If so, how?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2021-01-13 22:30:17 +0200

Lupp gravatar image

updated 2021-01-14 19:37:46 +0200

There are the functions INDIRECT(), OFFSET(), and sometimes also useful in related cases, ADDRESS().
The most flexible of the applicable functions is OFFSET().

edit flag offensive delete link more


Offset does exactly what I want to do. Thanks for the help.

tpollak gravatar imagetpollak ( 2021-01-14 16:51:45 +0200 )edit

Question Tools

1 follower


Asked: 2021-01-13 21:40:58 +0200

Seen: 62 times

Last updated: Jan 14