Forum Discussion

gerald_gl01's avatar
gerald_gl01
Copper Contributor
Nov 15, 2022
Solved

Average function Altered when dragging down / drop down

Hi, I am currently calculating averages every five cells:   =AVERAGE(B1:B5) =AVERAGE(B6:B10) =AVERAGE(B11:B15) When I do dragg down / drop down the pattern changes. The arguments within the...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Nov 15, 2022

    gerald_gl01 That's because you are actually copying three formulas down to the next three rows. The relative row references in the first formula B1:B5 are increased by 3, thus B4:B8. Similar for the other ones. B6:B10 becomes B9:B13 and B11:B15 becomes B14:B18.

     

    You would need to use an OFFSET function that "calculates" the next range of five cells, based on the current row number.

     

    Enter this in C1 and it should work:

    =AVERAGE(OFFSET($B$1,(ROW()-1)*5,0,5))

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 03, 2023

gerald_gl01 

This most likely doesn't match the needs of the OP but, to widen the discussion, you do not need to drag formulas using 365, the entire concept of relative referencing is unnecessary.

= LET(
    blocks, WRAPROWS(data, 4),
    BYROW(blocks, LAMBDA(b, AVERAGE(b)))
  )

An advantage of using arrays to return results is that they can be absolute.  Amongst other things, this means that when you copy formulas to scratch space to work on them, the results do not depend upon the location.

Resources