Forum Discussion
KasperThomsen
Oct 20, 2023Copper Contributor
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
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.
It's a pity that your screenshot doesn't show which column contains the average formulas.
- KasperThomsenCopper ContributorI'm sorry about that, I will edit the post, so you can see it
Unfortunately, you omitted the column headings (A, B, C, ...) this time.
And which column is the relevant one? I don't know Danish.