Problem with FORECAST formula.

S.H.A.D.O.

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
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...
Code: 
=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...

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

I hope this makes sense.

Thanks in advance.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Assuming this array formula in A1, which yields the number of the last row with value in column B.

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

Try this for your forecast.

Code: 
[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]
 
Upvote 0
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
Forecast >

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]1.7
[/TD]
[TD="align: right"]9.3
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]17.9
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]32
[/TD]
[TD="align: center"]50
[/TD]
[TD="align: center"]- 1
[/TD]
[TD="align: center"]41
[/TD]
[TD="align: center"] 9
[/TD]
[TD="align: center"]19
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"] 3
[/TD]
[TD="align: center"] 5
[/TD]
[TD="align: center"]44
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]22
[/TD]
[TD="align: center"]10
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]16
[/TD]
[TD="align: center"] 6
[/TD]
[TD="align: center"]44
[/TD]
[TD="align: center"]31
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]15
[/TD]
[TD="align: center"]37
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]21
[/TD]
[TD="align: center"]11
[/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]29
[/TD]
[TD="align: center"]40
[/TD]
[TD="align: center"]31
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]26
[/TD]
[TD="align: center"]47
[/TD]
[TD="align: center"]49
[/TD]
[TD="align: center"]43
[/TD]
[TD="align: center"]35
[/TD]
[TD="align: center"]38
[/TD]
[TD="align: center"]28
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"] 3
[/TD]
[TD="align: center"]38
[/TD]
[TD="align: center"] 5
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"] 9
[/TD]
[TD="align: center"]30
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]27
[/TD]
[TD="align: center"]29
[/TD]
[TD="align: center"]39
[/TD]
[TD="align: center"] 3
[/TD]
[TD="align: center"]44
[/TD]
[TD="align: center"] 2
[/TD]
[TD="align: center"] 6
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Forecast​

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

Thanks in advance.
 
Last edited:
Upvote 0
I don't know if this is clearer!

Excel 2007
A
B
C
D
E
F
G
H
I
J
K

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]1.7
[/TD]
[TD="align: right"]9.3
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]17.9
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] "]Forecast >
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] , align: center"]20
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] , align: center"]32
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] , align: center"]50
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] , align: center"]- 1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] , align: center"]41
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] , align: center"] 9
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D7E4BC]#D7E4BC[/URL] , align: center"]19
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"][/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"]1
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: right"][/TD]
[TD="bgcolor: #969696, align: center"]30
[/TD]
[TD="bgcolor: #969696, align: center"] 3
[/TD]
[TD="bgcolor: #969696, align: center"] 5
[/TD]
[TD="bgcolor: #969696, align: center"]44
[/TD]
[TD="bgcolor: #969696, align: center"]14
[/TD]
[TD="bgcolor: #969696, align: center"]22
[/TD]
[TD="bgcolor: #C0C0C0, align: center"]10
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"]2
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: right"][/TD]
[TD="bgcolor: #969696, align: center"]16
[/TD]
[TD="bgcolor: #969696, align: center"] 6
[/TD]
[TD="bgcolor: #969696, align: center"]44
[/TD]
[TD="bgcolor: #969696, align: center"]31
[/TD]
[TD="bgcolor: #969696, align: center"]12
[/TD]
[TD="bgcolor: #969696, align: center"]15
[/TD]
[TD="bgcolor: #C0C0C0, align: center"]37
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"]3
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: right"][/TD]
[TD="bgcolor: #969696, align: center"]21
[/TD]
[TD="bgcolor: #969696, align: center"]11
[/TD]
[TD="bgcolor: #969696, align: center"]17
[/TD]
[TD="bgcolor: #969696, align: center"]30
[/TD]
[TD="bgcolor: #969696, align: center"]29
[/TD]
[TD="bgcolor: #969696, align: center"]40
[/TD]
[TD="bgcolor: #C0C0C0, align: center"]31
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"]4
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: right"][/TD]
[TD="bgcolor: #969696, align: center"]26
[/TD]
[TD="bgcolor: #969696, align: center"]47
[/TD]
[TD="bgcolor: #969696, align: center"]49
[/TD]
[TD="bgcolor: #969696, align: center"]43
[/TD]
[TD="bgcolor: #969696, align: center"]35
[/TD]
[TD="bgcolor: #969696, align: center"]38
[/TD]
[TD="bgcolor: #C0C0C0, align: center"]28
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"]5
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: right"][/TD]
[TD="bgcolor: #969696, align: center"]13
[/TD]
[TD="bgcolor: #969696, align: center"] 3
[/TD]
[TD="bgcolor: #969696, align: center"]38
[/TD]
[TD="bgcolor: #969696, align: center"] 5
[/TD]
[TD="bgcolor: #969696, align: center"]14
[/TD]
[TD="bgcolor: #969696, align: center"] 9
[/TD]
[TD="bgcolor: #C0C0C0, align: center"]30
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"]6
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: right"][/TD]
[TD="bgcolor: #969696, align: center"]27
[/TD]
[TD="bgcolor: #969696, align: center"]29
[/TD]
[TD="bgcolor: #969696, align: center"]39
[/TD]
[TD="bgcolor: #969696, align: center"] 3
[/TD]
[TD="bgcolor: #969696, align: center"]44
[/TD]
[TD="bgcolor: #969696, align: center"] 2
[/TD]
[TD="bgcolor: #C0C0C0, align: center"] 6
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: center"]7
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: right"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

</tbody>
Forecast​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A1
[/TH]
[TD="align: left"]=SUMIF($A$2:$A$3,">0")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A2
[/TH]
[TD="align: left"]=COUNTIF(K15:K21,">0")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A3
[/TH]
[TD="align: left"]=COUNTIF(K22:K22,">0")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]E6
[/TH]
[TD="align: left"]=FORECAST(INDIRECT("$B"&$A1),OFFSET(E15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]F6
[/TH]
[TD="align: left"]=FORECAST(INDIRECT("$B"&$A1),OFFSET(F15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G6
[/TH]
[TD="align: left"]=FORECAST(INDIRECT("$B"&$A1),OFFSET(G15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]H6
[/TH]
[TD="align: left"]=FORECAST(INDIRECT("$B"&$A1),OFFSET(H15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]I6
[/TH]
[TD="align: left"]=FORECAST(INDIRECT("$B"&$A1),OFFSET(I15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]J6
[/TH]
[TD="align: left"]=FORECAST(INDIRECT("$B"&$A1),OFFSET(J15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]K6
[/TH]
[TD="align: left"]=FORECAST(INDIRECT("$B"&$A1),OFFSET(K15,,,$A1-1,1),OFFSET($B15,,,$A1-1,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]E10
[/TH]
[TD="align: left"]=FORECAST($B21,E15:E20,$B15:$B20)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]F10
[/TH]
[TD="align: left"]=FORECAST($B21,F15:F20,$B15:$B20)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G10
[/TH]
[TD="align: left"]=FORECAST($B21,G15:G20,$B15:$B20)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]H10
[/TH]
[TD="align: left"]=FORECAST($B21,H15:H20,$B15:$B20)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]I10
[/TH]
[TD="align: left"]=FORECAST($B21,I15:I20,$B15:$B20)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]J10
[/TH]
[TD="align: left"]=FORECAST($B21,J15:J20,$B15:$B20)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]K10
[/TH]
[TD="align: left"]=FORECAST($B21,K15:K20,$B15:$B20)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B16
[/TH]
[TD="align: left"]=B15+1
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B17
[/TH]
[TD="align: left"]=B16+1
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B18
[/TH]
[TD="align: left"]=B17+1
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B19
[/TH]
[TD="align: left"]=B18+1
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B20
[/TH]
[TD="align: left"]=B19+1
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B21
[/TH]
[TD="align: left"]=B20+1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Thanks in advance.
 
Last edited:
Upvote 0
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.

Code: 
=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.
 
Upvote 0
Good afternoon,

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

Code: 
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.
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Forecasting with VBA or Power Pivot in Excel
Replies
0
Views
641
Emilio30
E
M
  • Question Question
Change value of cells in a range based on value of another cell
Replies
7
Views
389
mkrass
M
R
  • Question Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
476
Domenic
D
jski21
  • Question Question
Reference Cell Contents in Range Formula
Replies
8
Views
412
jski21
jski21
M
  • Solved
Insert new row with incremental formula
Replies
8
Views
584
Sheik
S

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top