Forum Discussion

KasperThomsen's avatar
KasperThomsen
Copper Contributor
Oct 20, 2023

Absolute referencing when adding columns.

Hello experts,

I have been looking for an answer to this question for a couple of days now, without any luck. I hope you are able to help me.

I'm looking for at way to keep cell references when adding columns. This particular sheet is a monthly finance sheet, where 32 rows are filled out in a new column every month (see picture)

 

With this data we take the average of the last 4 months, for some KPI's.
Right now we add the column, add the data, and change all the formulas to represent the last 4 months.
Is there any way to do this automatically/less manual
I thought about doing it top down, and using INDIRECT to take the top 4 values, but because of the amount of rows, that now turn to columns, this is very impractical.

 If anybody knows some trick to do this, or have an idea on how to get around this issue, I would love to hear it.

Thank you in advance,
Kasper 

  • HansVogelaar's avatar
    HansVogelaar
    Oct 20, 2023

    KasperThomsen 

     

    Use

     

    =AVERAGE(OFFSET(X2, 0, -13, 1, 4))

     

    and fill or copy down. This means: take the average of the range starting 13 cells to the left of X2, i.e. in K2, and 4 columns wide.

Resources