Search code examples
averagelibreofficelibreoffice-calc

How to represent cell in libreoffice calc like B(2+4)?

I need to do something in LibreOffice calc, but it depends on the number of the cell, so I would like to do something like this to represent the cell =AVERAGE(B(0x4+1):(1x4)), but it doesn't work. Can you help me?

Solution

  • You can use the function INDIRECT to get a cell reference or range reference from text.

    For example put this into A1:

    =AVERAGE(INDIRECT("B"&(4*(ROW(A1)-1)+1)&":"&"B"&(4*ROW(A1))))

    Here I use the row number as the value for your 0 and 1, respectively.

    • ROW(A1) returns 1
    • "B"&(4*(ROW(A1)-1)+1) returns "B1"
    • "B"&(4*ROW(A1)) returns "B4"
    • Both concatenated together with ":" in the middle returns "B1:B4"
    • If you have A2 instead of A1, the text will result in "B5:B8"

    If you copy this formula into A2 and following as many times as you see fit, you will get your averages.

    Disclaimer: I tried this with a non-English version of LibreOffice 7.1 and translated the function names. They might be wrong.