Ask Your Question
0

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

asked 2021-04-19 11:26:01 +0200

Pantona gravatar image

updated 2021-04-19 11:30:02 +0200

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2021-04-19 11:42:34 +0200

Opaque gravatar image

updated 2021-04-19 11:47:42 +0200

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.

edit flag offensive delete link 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. :-)

Pantona gravatar imagePantona ( 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)

Lupp gravatar imageLupp ( 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.

Lupp gravatar imageLupp ( 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

Opaque gravatar imageOpaque ( 2021-04-19 12:26:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-04-19 11:26:01 +0200

Seen: 22 times

Last updated: Apr 19