Range break in FORECAST function, Ms Excel

Question

Instead of large No of rows, I divided my table into 4 columns. Now i cannot select multiple ranges for FORECSAT function.

Answer 1

Hi

Let's count the number of arguments in the FORECAST function:

FORECAST(x, known_y's, known_x's)

x => 1; known_y's=> 2; known_x's => 3        Total => 3 arguments

Let's count the number of arguments in your FORECAST formula:

FORECAST(G6**,** B2:B27**,D2D26,** A2:A27**,** C2:C26)

G6 => 1; B2:B27 => 2; D2:D26 => 3; A2:A27 => 4; C2:C26 => 5 Total => 5 arguments

Where's the error/mistake (not mentioning you didn't provide the error message you got, that's definitively not "Not working")?

Hope this all makes sense & helps. All the best...

Answer 2

Hi

Welcome to post back if you still need help on the function.

Regards,

Clark

Answer 3

Excel 365 Pro Plus

An obvious mathematical game

with numbers that match an exponential Bleasdale curve

with an R^2 correlation of 1.0000.

Forecast.Linear() and vintage Forecast() of Excel <2013 are linear and ruled out.

Forecast.ETS() does a better job, but still no match for Bleasdale.

The numbers could be the start of an s logistic curve,

but more business data is needed.

http://www.mediafire.com/file/rkeenjp0gua8ypq/01_28_20a.xlsx/file

http://www.mediafire.com/file/ibg41clygdmd05h/01_28_20a.pdf/file

Answer 4

The following does __not__ work in my older version of Excel.  But you might give it a try, with low expectations.

=FORECAST(G6, (B2:B27,D2:D26), (A2:A27,C2:C26))

The syntax (B2:B27,D2:D26) is a range union.  Unfortunately, it works with very few Excel functions (e.g. IRR), at least in my older version of Excel.

Alternatively, the following __does__ work (normally enter; just press Enter as usual).  But note the limitation explained below.

=FORECAST(G6, CHOOSE({1,2},B2:B26,D2:D26), CHOOSE({1,2},A2:A26,C2:C26))

Note that the ranges must be same length.

To that end, I had to omit B27 and A27.  Hopefully, that has minimal impact on the FORECAST results (i.e. the linear regression).

Alternatively, you might consider adding an estimated data point between 49 and 50. That is, insert a row before C26:D26, pushing C26:D26 down to C27:D27.  Then, the estimated new C26 is =AVERAGE(C25,C27).  The estimated new D26 is =AVERAGE(D25,D27).  Again, hopefully that has minimal impact on the FORECAST results.

Then, the FORECAST formula is:

=FORECAST(G6, CHOOSE({1,2},B2:B27,D2:D27), CHOOSE({1,2},A2:A27,C2:C27))

Do any of those ideas work for you?

(If not, my final suggestion would be a VBA user-defined function that returns the union of ranges.  TBD, if you want it.)