Error with Row() or Column() within Offset() within Intercept()

[jfsknight]

I'm having an issue with the following formula:

INTERCEPT(OFFSET($AM$6,0,0,1,MonthOffset-COLUMN($AM$6)+3),OFFSET($AM$1,0,0,1,MonthOffset-COLUMN($AM$1)+3))

MonthOffset is just an integer, 72 in this case.

The above formula works fine with LINEST, rather than INTERCEPT. The issue is with the Column($AM$1) command which returns a {39} rather than just 39. It returns 39 if within the LINEST function. OFFSET returns a {#VALUE!} error if the width argument is in curly brackets.

When I pull the OFFSET part into its own cell, it works fine.


The full formula is:

=LINEST(OFFSET($AM$6,0,0,1,MonthOffset-COLUMN($AM$6)+3),OFFSET($AM$6,0,0,1,MonthOffset-COLUMN($AM$6)+3))*D$1+INTERCEPT(OFFSET($AM$6,0,0,1,MonthOffset-COLUMN($AM$6)+3),OFFSET($AM$1,0,0,1,MonthOffset-COLUMN($AM$1)+3))

Like I said before, the first half is working fine.

Does anyone have an idea of what's happening?

 

[XOR LX]

Hi.

Firstly, not sure I understand.

Since the reference in COLUMN($AM$6) is an absolute reference, i.e. it will not change when this formula is copied to other cells, why can't it simply be replaced with the value 39?

Regards

 

[jfsknight]

Hi.

Firstly, not sure I understand.

Since the reference in COLUMN($AM$6) is an absolute reference, i.e. it will not change when this formula is copied to other cells, why can't it simply be replaced with the value 39?

Regards

I'm trying to get away from doing that so that if anybody inserts a column into the worksheet it won't affect the formulas. The likelihood of someone inserting a column is low, but in the future this file may be handed off to someone else.

One thing that works is this:

=LINEST($AM$6:INDIRECT(ADDRESS(ROW($C$6),MonthOffset+COLUMN($C$6)-1)),$AM$1:INDIRECT(ADDRESS(ROW($C$1),MonthOffset+COLUMN($C$6)-1)))*E$1+INTERCEPT($AM$6:INDIRECT(ADDRESS(ROW($C$6),MonthOffset+COLUMN($C$6)-1)),$AM$1:INDIRECT(ADDRESS(ROW($AM$1),MonthOffset+COLUMN($C$6)-1)))

but if I could replace 12 commands with 6 in each cell that would be optimal.

I just find it odd that the same function with the same arguments returns two different results, depending on which outer function it's enclosed within.

 

[XOR LX]

I'm trying to get away from doing that so that if anybody inserts a column into the worksheet it won't affect the formulas. The likelihood of someone inserting a column is low, but in the future this file may be handed off to someone else.

??

1) An absolute reference will still change when a column is inserted!
2) If the value was fixed at a numerical value of 39 (which is precisely the value of that construction), then that certainly wouldn't be affected by any column insertions!

Regards