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

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?

edit retag close merge delete

Sort by » oldest newest most voted

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). Hope that helps. more ## Comments 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. :-) ( 2021-04-19 11:53:05 +0200 )edit 1 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 ...(more)

( 2021-04-19 12:08:39 +0200 )edit

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.

( 2021-04-19 12:16:51 +0200 )edit

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

( 2021-04-19 12:26:17 +0200 )edit