Calc: How to properly use OFFSET to the right with increasing Interval?

[Pantona]

Assumed we have this table style in sheet 1:

     A     B     C     D     E     F     G     H
1   0.1   0.2   0.5   0.7   0.4   0.5   0.9   1.0
2
3

In sheet 2 there have to be averages made out of the paired cells of sheet 1, means averages from A1:B1, C1:D1, E1:F1, and so on.

• AVERAGE(A1:B1) from sheet 1 should be in sheet 2: cell A1,

• AVERAGE(C1:D1) from sheet 1 should be in sheet 2: cell B1,

• AVERAGE(E1:F1) from sheet 1 should be in sheet 2: cell C1, and so on…

        A      B      C       D
   1   0.15   0.6    0.45    0.95 
   2
   3
  

My assumption was to simply use the OFFSET-function with some command like that:

=OFFSET(AVERAGE(A1:B1),0,(COLUMN()-1)*2))

As the rows do not get switched, we can put a 0 into the row move option.

Unfortunately, this command does not work as expected. Any ideas?

[anon73440385]

Hello

you probably want: =AVERAGE(OFFSET(Sheet1.$A1;0;(COLUMN()-1)*2;1;2))

Hint

Function OFFSET requires an address (reference) as a first argument, but you provided a value (i.e. AVERAGE(A1:B1) is a numerical value and not a reference).

Ref.: LibreOffice Help - OFFSET

Hope that helps.

[Pantona]

It works! Awesome, thank you very much!

However, I am a little bit confused about the multiple numbers behind the COLUMN(), could you add some explanation maybe? The OFFSET-help is not pretty good actually, I’ve checked that already.

[Lupp]

Just in addition:
-1- An address is a text by type. Offset actually requires a reference as its first argument. If an actual address should be used, it must be enclosed in a call of the INDIRECT() function.
-2- The 1 in (COLUMN()-1)*2 is the special value representing what would look (COLUMN(A1)-COILUMN($A1))*2 in a more generalized example assuming the formula is first applied in column A, and then filled to the right. In fact the formula needs to generate the sequence 0;2;4;.. when filling it to the right.
-3- Generally the design of a sheet needing a calculation as requested here should be reconsidered. It indicates that what’s given as data in ROW 1 aren’t just numbers one by one, but pairs of specifically related numbers. Keeping such data the given way will induce similar problems again and again as soon as additional calculations will be needed. More detailed advice would require detailed information abou the context.

[Lupp]

Concerning the term ADDRESS:
Like much of the terminology for spreadsheets it is often used in unclear or misleading ways.
In fact what looks like an address in most formulas is just a human-readable representation of a reference, only used for the UI. Internally it is represented in a way better understood when using the so-called RC-representation (which is unfortunately not very handy). The result returned by the ADDRESS() function actually is an address, and needs INDIRET() to convert it to a referenece.

[anon73440385]

I am a little bit confused about the multiple numbers behind the COLUMN()

They are not behind but part of the OFFSET() argument list.

The result of `OFFSET()` evaluation is a range and a cell range has three elements:

• starting point (left upper cell of the range), which is determined by Sheet1.$A1;0;(COLUMN()-1)*2

• number of rows of the cell range, which is determined by ;1 - the first additional number and in terms of the help page called Height

• number of columns of the cell range which is determined by ;2 - the second additional number and in terms of the help page called Width