Geek Answers Handbook

Excel OFFSET function error when using TREND in link

Consider an Excel worksheet with the following values:

AB -------- 1| 1 5 2| 2 8 3| 3 11

Entering the array formula =TREND(B1:B3,A1:A3,A1:A3) in cells C1: C3 returns {5;8;11} , as expected. Entering =OFFSET(C1:C3,1,0) returns {8;11;0} , as expected.

However, if I try to enter =ROWS(OFFSET(TREND(B1:B3,A1:A3,A1:A3),1,0)) , I get the message: "The entered formula contains an error." I can do nothing to make him accept the formula.

Then I created the named ranges "TrendRange" and "TrendFormula" with the formulas =Sheet1!$C$1:$C$3 and =TREND(Sheet1!$B$1:$B$3,Sheet1!$A$1:$A$3,Sheet1!$A$1:$A$3) respectively.

Again, =OFFSET(TrendRange,1,0) gives the correct result, but =OFFSET(TrendFormula,1,0) does not (this leads to #VALUE! ).

There is a simple test that seems to always identify when an OFFSET problem will have this problem. If =CELL("address", xxx) or =AREAS(xxx) lead to #VALUE! , then section xxx cannot be used in OFFSET.

Is there any way around this? I tried to use LINEST and SLOPE / INTERCEPT instead of TREND, but I get the same result.

0
source share
1 answer

OFFSET requires a reference (cell or range as the first argument), it will not accept an array, since the formula is of type

=TREND(B1:B3,A1:A3,A1:A3)

always returns an array, you cannot use it as the first argument to OFFSET. Is this related to the previous question?

+1
source

More articles:


All Articles