LibreOffice Calc: How to use OFFSET Function when Selection Intervals should increase?

Question

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 of 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?

Answer 1

You can try the steps below. I have modified your formula a little bit. The OFFSET function is performed first; allowing to shift the cells based on their column. Then, the average is calcualted.

• On A1, keep the average formula:

=AVERAGE(A1:B1)

• On B1, enter the following formula on the next cell:

=AVERAGE(OFFSET(A1:B1,0,COLUMN()))

• Drag B1 to the left to fill the other cells

Note: You need to modify the formula to take into account when there are empty cells as you will get #DIV/0 or other errors.