0

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

P E
  • 165
  • 11
  • Can you show some sample data? Right now you feeding the function different siced array which will return `#N/A` – JvdV May 17 '19 at 12:13
  • Hi JvdV, I've added some representative data (much briefer that the real thing). Does that clarify my question? – P E May 20 '19 at 09:29
  • I'm sorry but I'm still equally confused. Like I said you can't feed unequal arrays to `FORECAST()` function. It will throw `#N/A` error. How did you derive to `11.71; 14.43; 177.71; 25.71; 16.71; 10.86`? Average of each row? – JvdV May 20 '19 at 11:16
  • If you put ````FORECAST.LINEAR($H$1,A2:G2,$A$1:$G$1)```` in cell H2 and use the fill handle to drag the formula down the column, you'll get the ````FORECAST```` result for each row. But as I mentioned, doing separate calculations for each row isn't an option in my situation: I need the results delivered as a 1-dimensional array. Clearly ````FORECAST```` doesn't want to work that way; my question is whether there's some way to 'force' the desired behaviour, just as there is (for example) with ````INDEX````. – P E May 20 '19 at 11:28
  • And in your original question you got 6 rows of data for 6 columns. Would it work for you to get all Forecasts of the 6 rows in the array? So that way your array will have 6 items in it being equal to the columns? – JvdV May 20 '19 at 11:37
  • Not sure I completely understand your question, but if you're asking what I think you are the answer's 'yes'. What I want (if possible) is a ````FORECAST```` formula in a single cell (on a completely different sheet) to give me an array that represents the forecasts for each of the 6 rows. (In reality there'll be a lot more than 6 rows.) And I need this as an array because I'll be multiplying several arrays together to get to the ultimate result I need. – P E May 20 '19 at 11:45

1 Answers1

1

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?

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks for your time & interest, JvdV--much appreciated! I'm getting pulled to another priority right now but as soon as I can (tonight? tomorrow morning?) I'll come back and spend some time going over your solution, so I can give it the attention it deserves. Thanks again... – P E May 20 '19 at 13:32
  • Real sorry for my delay in responding. Thanks again to JvdV for steering me in the right direction for what I needed. I had to adapt what he suggested because (in the interests of space, etc) I probably didn't fully explain my predicament. Bottom line is that I did need to write a UDF to do what I wanted. Thanks again so much. – P E Jun 21 '19 at 08:01