Problem with FORECAST formula.

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

Good evening,

I am having trouble with a FORECAST formula.

This formula works when the cells are hardcoded into the formula and the fotmula is copied across...

=FORECAST($B2297,E15:E2296,$B15:$B2296)

Now I have a calculated figure which resides in cell A1 and changes when a new row of figures is added to the bottom of the data.

I want to adapt the formula so that when a new row of data is added to the bottom it takes that extra row of data into consideration for the new forecast.

Basically, I want something like this...

=FORECAST($A1 + 1,E15:E & A1 + 14,$B15:$B & A1 + 14)

I hope this makes sense.

Thanks in advance.

 

[estevaoba]

Assuming this array formula in A1, which yields the number of the last row with value in column B.

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=MAX(IF(B:B<>"",ROW(B:B)))[/TD]
[/TR]
</tbody>[/TABLE]

Try this for your forecast.

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=FORECAST(INDIRECT("B"&A1),OFFSET(E15,,,A1-1,1),OFFSET(B15,,,A1-1,1))[/TD]
[/TR]
</tbody>[/TABLE]

 

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

Thanks for the reply estevaoba, it is appreciated.

Unfortunately it does not give me the correct answer.
My forecast are in cells E10:K10 (hardcoded) and your forecast are in cells E6:K6.

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	6
2	6
3
4
5
6					28	1.7	9.3	50	14	26	17.9
7		Forecast >
8
9
10					20	32	50	- 1	41	9	19
11
12
13
14
15		1			30	3	5	44	14	22	10
16		2			16	6	44	31	12	15	37
17		3			21	11	17	30	29	40	31
18		4			26	47	49	43	35	38	28
19		5			13	3	38	5	14	9	30
20		6			27	29	39	3	44	2	6
21		7

<tbody>
</tbody>

Forecast​

Worksheet Formulas Cell Formula A1 =SUMIF($A$2:$A$3,">0") A2 =COUNTIF(K15:K21,">0") A3 =COUNTIF(K22:K22,">0") E6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(E15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) F6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(F15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) G6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(G15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) H6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(H15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) I6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(I15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) J6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(J15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) K6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(K15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) E10 =FORECAST($B21,E15:E20,$B15:$B20) F10 =FORECAST($B21,F15:F20,$B15:$B20) G10 =FORECAST($B21,G15:G20,$B15:$B20) H10 =FORECAST($B21,H15:H20,$B15:$B20) I10 =FORECAST($B21,I15:I20,$B15:$B20) J10 =FORECAST($B21,J15:J20,$B15:$B20) K10 =FORECAST($B21,K15:K20,$B15:$B20) B16 =B15+1 B17 =B16+1 B18 =B17+1 B19 =B18+1 B20 =B19+1 B21 =B20+1 <tbody> </tbody>

<tbody>
</tbody>

Thanks in advance.

 

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

I don't know if this is clearer!

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	6
2	6
3
4
5
6					28	1.7	9.3	50	14	26	17.9
7		Forecast >
8
9
10					20	32	50	- 1	41	9	19
11
12
13
14
15		1			30	3	5	44	14	22	10
16		2			16	6	44	31	12	15	37
17		3			21	11	17	30	29	40	31
18		4			26	47	49	43	35	38	28
19		5			13	3	38	5	14	9	30
20		6			27	29	39	3	44	2	6
21		7

<tbody>
</tbody>

Forecast​

Worksheet Formulas Cell Formula A1 =SUMIF($A$2:$A$3,">0") A2 =COUNTIF(K15:K21,">0") A3 =COUNTIF(K22:K22,">0") E6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(E15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) F6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(F15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) G6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(G15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) H6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(H15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) I6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(I15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) J6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(J15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) K6 =FORECAST(INDIRECT("$B"&$A1),OFFSET(K15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1)) E10 =FORECAST($B21,E15:E20,$B15:$B20) F10 =FORECAST($B21,F15:F20,$B15:$B20) G10 =FORECAST($B21,G15:G20,$B15:$B20) H10 =FORECAST($B21,H15:H20,$B15:$B20) I10 =FORECAST($B21,I15:I20,$B15:$B20) J10 =FORECAST($B21,J15:J20,$B15:$B20) K10 =FORECAST($B21,K15:K20,$B15:$B20) B16 =B15+1 B17 =B16+1 B18 =B17+1 B19 =B18+1 B20 =B19+1 B21 =B20+1 <tbody> </tbody>

<tbody>
</tbody>

Thanks in advance.

 

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

Good morning,

I have managed to crack it!

I needed to add 15 to the ("B"&$A1+15) because of the position of the next number to evaluate on and take out the -1 from the known_y’s and kmown_x’s.

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

I am going to see if I can write a bit of code to do this so the new FORECAST values are entered into cells E10:K10 and M10:S10. That way I can just click a button after I have inputted new data at the bottom of the table.

Thanks in advance.

 

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

Good afternoon,

I managed to put some code together to achieve this...

Sub test()
    Range("E10:S10").ClearContents
    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"
End Sub

Thanks.

 

[estevaoba]

Good!

As to your post #4 , I see that if you have A1=MAX(IF(B:B<>"",ROW(B:B)))-14, the range by formula OFFSET(B15,,,A1-1,1) would be the same as B15:B2296, as you originally were trying to get.

But, any way, I'm glad you got it figured out.

Godspeed!

 

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

estevaoba.