CODEWITHSUNDEEP
arraysexcelforecast
P E
P E

Reputation: 187

Is there a way to use a 2-dimensional array as the Y-argument in Excel's FORECAST functions?

Excel's FORECAST functions take a 1-dimensional array for both the 'known Xs' argument and the 'known Ys' argument, and then returns a single value as the answer.

I'd like to use a 2-dimensional array for the 'known Ys' argument, and return an array (1-dimensional) as the answer. In other words, I want to return a set (array) of forecasts that correspond to a set (array) of Y-values, covering the same time-scale (X-values). (There's a reason I need this...I need to multiply the result I get by a couple of other arrays.)

But if I take a formula that works fine, like FORECAST.LINEAR($H$1,A2:G2,$A$1:$G$1) and then change the 1-dimensional array to 2-dimensional (G7 instead of G2): FORECAST.LINEAR($H$1,A2:G7,$A$1:$G$1) and press Ctrl+Alt+Enter, I get an error (#N/A). Same with the TREND function.

I know some Excel functions don't like taking an array as an argument--though sometimes there are ways around this (like here: Can Excel's INDEX function return array?). But I can't figure out if it's possible to 'dereference' things in my situation...I certainly haven't managed to incorporate this approach here.

Addendum in response to comment: The data below are representative (though the real data have a lot more rows and columns!). The top row represents the 'known X's (this is a time scale) and the subsequent rows are the data. The result I want to end up with is an array representing the forecasted Y-value corresponding to X=8...here, I believe that would be 11.71; 14.43; 177.71; 25.71; 16.71; 10.86;

enter image description here

Upvotes: 0

Views: 712

Answers (1)

JvdV
JvdV

Reputation: 75890

So here is an attempt, I'm still not sure what you are after, but let me try.

I've went the UDF way and my assumption is that you feed the function with a range that has as many rows as columns.

Function GetResult(RNG1 As Range, RNG2 As Range) As Double

Dim X As Double, RNG3 As Range, ARR() As Variant
ReDim ARR(RNG2.Rows.Count - 1)
For X = 1 To RNG2.Rows.Count
    Set RNG3 = Application.Intersect(RNG2, Rows(X + 1))
    ARR(X - 1) = Application.WorksheetFunction.Forecast(RNG2.Columns.Count + 1, RNG3, RNG1)
Next X

GetResult = Application.WorksheetFunction.Forecast(RNG2.Columns.Count + 1, ARR, RNG1.Value)

End Function

On the sample data you give (with the extraction of column 7), it would look like this:

enter image description here

The function will create a forecast for each line and stores it in an 1-dimensional array. Then it will use all these forecasts to create a final forecast.

Is this close to what you expect?

Upvotes: 1

Related Questions