Ask Your Question

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

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

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 +0100

Lupp gravatar image

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

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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 18 times

Last updated: Jan 14