Excel FORECAST on pre-determined number.

[S.H.A.D.O.]

Good afternoon,

I have a two ranges of data in cells E15:K? And M15:S?
I have a figure in cell A1 that increases by one when new data is added to the bottom of the ranges above.
The code below works and does exactly what I want it to do...

Sub FORECAST_Next_Numbers()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
   
    Range("E10").Resize(1, 7).Formula = _
            "=FORECAST(INDIRECT(""B"" & $A1+15),OFFSET(E15,,,$A1,1),OFFSET($B15,,,$A1,1))"
           
    Range("M10").Resize(1, 7).Formula = _
            "=FORECAST(INDIRECT(""B"" & $A1+15),OFFSET(M15,,,$A1,1),OFFSET($B15,,,$A1,1))"
           
    Range("E10:S10").Value = Range("E10:S10").Value
    Range("E10:K10,M10:S10").NumberFormat = "0"
   
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

I want to be able to forecast from the bottom of the data up by the figure in cell B1 as opposed to the whole of the data.
So for example, if I had the number 50 in cell B1, I just want it start at the last figure in column E and calculate the FORECAST up on the last 50 numbers.

I hope this makes sense.

Thanks in advance.

 

[Kenneth Hobson]

Have you considered just using an array formula manually entered? Use the times -1 trick for the Offset() of the last cell to get the beginning cell as I explained earlier.

If needed, I can show you how to construct the array formula.

See the links near the bottom in this link if you want a tool to help paste data nicely. I need to look into using that method on this forum more myself. https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[S.H.A.D.O.]

Thanks for the reply Kenneth,

If you could point me in the right direction to the array formula it would be appreciated as I am not following what you are saying.
The reason the figure is in cell B1 is because I will want to change it to test different results.

Thanks in advance.

 

[Kenneth Hobson]

The first input is the number to predict. Where is that one? Don't worry about offsets. Just do the formula manually and paste it. e.g. =TREND(ys,xs,5) or =FORECAST(5,ys,xs)

FYI: Trend() replaced Forecast(). It lets you use an array of x's to predict the y's. So Trend() can be an array formula. e.g. =TREND(ys,xs,xs)

Some moderators don't like it but I like files to work with to help the user more. Either a link to one or a pasted range of data by the link that I showed might show what you need better.

I have to attend an event but I will check back later tonight and help more if needed.

 

[S.H.A.D.O.]

This is what I am trying to achieve...

Go to the last cell with a number in it in column E.
Use that cell and the 9 above it to forecast what the next number in column E is.
Put that number in cell E10.

The reason I want it to reference B1 is so I can test different sets of numbers, i.e. what the predicted value is using the last 10 numbers, 50 numbers, 125 numbers etc.

When I started this I didn't realise that it was going to be so involved!

Thanks in advance.

 

[S.H.A.D.O.]

Here is a screen shot...

Forecast

	A	B	C	D	E	F	G	H	I	J	K	L
1	2285	10
2
3
4
5
6
7
8
9
10
11
12
13
14
2289		2275
2290	2276	2276			6	40	43	58	47	22	42
2291	2277	2277			15	22	27	12	11	44	7
2292	2278	2278			41	37	44	59	31	3	32
2293	2279	2279			3	11	58	1	49	37	21
2294	2280	2280			15	55	58	42	8	33	53
2295	2281	2281			19	18	14	13	36	21	53
2296	2282	2282			35	20	29	53	23	6	57
2297	2283	2283			25	42	32	49	17	2	4
2298	2284	2284			16	27	41	49	30	4	54
2299	2285	2285			22	11	36	2	59	51	7
2300		2286
2301		2287
2302		2288

<TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula A1 =MAX(A15:A2315) A2289 =IF(K2289>0,B2289,"") B2289 =B2288+1 A2290 =IF(K2290>0,B2290,"") B2290 =B2289+1 A2291 =IF(K2291>0,B2291,"") B2291 =B2290+1 A2292 =IF(K2292>0,B2292,"") B2292 =B2291+1 A2293 =IF(K2293>0,B2293,"") B2293 =B2292+1 A2294 =IF(K2294>0,B2294,"") B2294 =B2293+1 A2295 =IF(K2295>0,B2295,"") B2295 =B2294+1 A2296 =IF(K2296>0,B2296,"") B2296 =B2295+1 A2297 =IF(K2297>0,B2297,"") B2297 =B2296+1 A2298 =IF(K2298>0,B2298,"") B2298 =B2297+1 A2299 =IF(K2299>0,B2299,"") B2299 =B2298+1 A2300 =IF(K2300>0,B2300,"") B2300 =B2299+1 A2301 =IF(K2301>0,B2301,"") B2301 =B2300+1 A2302 =IF(K2302>0,B2302,"") B2302 =B2301+1 <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

This is the formula that works on the whole of the data in column E down to A1 + 15 (15 is the row that the data starts on).

    Range("E10").Resize(1, 7).Formula = _
            "=FORECAST(INDIRECT(""B"" & $A1+15),OFFSET(E15,,,$A1,1),OFFSET($B15,,,$A1,1))"

For FORECAST purposes I want to adapt it to find the cell with data in the last row (column E for example), select that cell and also the 9 cells above it which makes 10 and is the figure in cell B1.

I basically want to be able to FORECAST from the last cell in the column up by the figure in cell B1.

I hope this helps!

 

[S.H.A.D.O.]

I tried to adapt the formula and came up with this, but unfortunately it doesn't work...

=FORECAST(INDIRECT("B" & $A1+15),OFFSET(E15,$A1+15-$B1,0,$B1,1),OFFSET($B15,$A1+15-$B1,0,$B1,1))

Does anyone have any ideas why this doesn't work please?

I thought if I could get a formula to work then I can incorporate that into code as I did in my OP.

Thanks in advance.

 

[S.H.A.D.O.]

I have come up with a psudo formula using TREND...

=TREND(E & $A1 + 15 - $B1:E & $A1 + 15,B & $A1 + 15 - $B1:B & $A1 + 15,B & $A1 + 15:B & $A1 + 15)

 

[Kenneth Hobson]

I think post #15 may help but is column A or B the x's? I am not sure that it matters since the values are the some for the ys data rows.

I gather that you are wanting to find the y when A1=285 (x) for each y's set in columns E:K? Earlier you said that A1 was the total number of data rows where 15+A1 would be that last row. That does not add up in your example 15+285=300, not 299.

Normally, if I want to look at bias, I would put xs in A, ys, in B, and predicted ys in C. B1-C1 would then be the first bias.

I'll work up the Indirect() string and see if we can't get closer to a final all formula solution.

 

[Kenneth Hobson]

It did not make sense to me to put an array of xs as 3rd parameter as your array would need to be selected over that same number of rows. You wanted 7 columns.

Here is your last post with indirect but only one known x to find the y.
=TREND(INDIRECT("E"&$A1+15-$B1 & ":E"&$A1+14),INDIRECT("B"&$A1+15-$B1&":B"&$A1+14),$A1)

Notice how I subtracted one for the last row. 15-1=14. That was based on your example data.

To construct a string for indirect, here is an easy way to see your result quickly though Evaluate the formula in steps works too.
="E"&$A1+15-$B1 & ":E"&$A1+14
="B"&$A1+15-$B1&":B"&$A1+14

Of course this method does not lend itself well to easy drag and fill since we used hard coded column letters.

 

[S.H.A.D.O.]

Thanks Kenneth,

Because in my example I wanted the FORECAST for 2286 I just increased the $A1 at the end of the formula by 1, then it worked.

=TREND(INDIRECT("E"&$A1+15-$B1 & ":E"&$A1+14),INDIRECT("B"&$A1+15-$B1&":B"&$A1+14),$A1+1)

I see what you mean by...

Of course this method does not lend itself well to easy drag and fill since we used hard coded column letters.

Anyway, I converted this to code and came up with...

Sub TREND_Next_Numbers()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Range("E10:S10").ClearContents
    Range("E10").Formula = _
        "=TREND(INDIRECT(""E""&$A1+15-$B1 & "":E""&$A1+14),INDIRECT(""B""&$A1+15-$B1&"":B""&$A1+14),$A1+1)"
    Range("F10").Formula = _
        "=TREND(INDIRECT(""F""&$A1+15-$B1 & "":F""&$A1+14),INDIRECT(""B""&$A1+15-$B1&"":B""&$A1+14),$A1+1)"
    Range("G10").Formula = _
        "=TREND(INDIRECT(""G""&$A1+15-$B1 & "":G""&$A1+14),INDIRECT(""B""&$A1+15-$B1&"":B""&$A1+14),$A1+1)"
    Range("H10").Formula = _
        "=TREND(INDIRECT(""H""&$A1+15-$B1 & "":H""&$A1+14),INDIRECT(""B""&$A1+15-$B1&"":B""&$A1+14),$A1+1)"
    Range("I10").Formula = _
        "=TREND(INDIRECT(""I""&$A1+15-$B1 & "":I""&$A1+14),INDIRECT(""B""&$A1+15-$B1&"":B""&$A1+14),$A1+1)"
    Range("J10").Formula = _
        "=TREND(INDIRECT(""J""&$A1+15-$B1 & "":J""&$A1+14),INDIRECT(""B""&$A1+15-$B1&"":B""&$A1+14),$A1+1)"
    Range("K10").Formula = _
        "=TREND(INDIRECT(""K""&$A1+15-$B1 & "":K""&$A1+14),INDIRECT(""B""&$A1+15-$B1&"":B""&$A1+14),$A1+1)"
    Range("E10:K10").Value = Range("E10:K10").Value
    Range("E10:K10").NumberFormat = "0"
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

I will investigate a way maybe to use a variable for each of the hard coded column letters to shorten the code and get it to loop through when I get some time.

The reason for the code is to produce the values only so that I can see when I put the next row of data in how close the predicted figures were. Then it is just a case of clicking a button to produce the next set of predicted results.

The formulas seem to work anyway, thank you so much Kenneth for your time and help on this, it is appreciated.