forecast formula question
 

Hi,

I'm trying to forecast a set of sales of a period of months, but I can't
decide whether my known X's and known Y's should be absolute references, or
whether the last month in the known x's and known y's should be relative.

for example
I have the following known x's and known Y's( x being the month, Y being the
sales value)
A B
Month X's Known Y
1 6
2 7
3 9
4 15
5 25
6 3
7 2
8 21
9 20


If I want to Forecast for month 10, I have the following formula

= Forecast(10; known Y's; Known X's)

If I was Starting in Cell A1 the formula would be

Forecast(A10;$B$1:B9;$A$1:A9)

What if I want to forecast further i.e month 11 - Do I include my
Forecasted Y value from month 10 as a known Y value

does that mean the formula becomes

Forecast(A11;$B$1:B10;$A$1:A10)

Or should the formula be


Forecast(A11;$B$1:$B$9;$A$1:$A$9)


I'd really apprececiate any help /advice anyone can offer, as I'm really
stuck with this

Regards

Daniel

Forecast(A10;$B$1:B9;$A$1:A9)
is correct

"Dan_Green" wrote:

Hi,

I'm trying to forecast a set of sales of a period of months, but I can't
decide whether my known X's and known Y's should be absolute references, or
whether the last month in the known x's and known y's should be relative.

for example
I have the following known x's and known Y's( x being the month, Y being the
sales value)
A B
Month X's Known Y
1 6
2 7
3 9
4 15
5 25
6 3
7 2
8 21
9 20


If I want to Forecast for month 10, I have the following formula

= Forecast(10; known Y's; Known X's)

If I was Starting in Cell A1 the formula would be

Forecast(A10;$B$1:B9;$A$1:A9)

What if I want to forecast further i.e month 11 - Do I include my
Forecasted Y value from month 10 as a known Y value

does that mean the formula becomes

Forecast(A11;$B$1:B10;$A$1:A10)

Or should the formula be


Forecast(A11;$B$1:$B$9;$A$1:$A$9)


I'd really apprececiate any help /advice anyone can offer, as I'm really
stuck with this

Regards

Daniel

Thanks bj

"bj" wrote:

Forecast(A10;$B$1:B9;$A$1:A9)
is correct

"Dan_Green" wrote:

Hi,

I'm trying to forecast a set of sales of a period of months, but I can't
decide whether my known X's and known Y's should be absolute references, or
whether the last month in the known x's and known y's should be relative.

for example
I have the following known x's and known Y's( x being the month, Y being the
sales value)
A B
Month X's Known Y
1 6
2 7
3 9
4 15
5 25
6 3
7 2
8 21
9 20


If I want to Forecast for month 10, I have the following formula

= Forecast(10; known Y's; Known X's)

If I was Starting in Cell A1 the formula would be

Forecast(A10;$B$1:B9;$A$1:A9)

What if I want to forecast further i.e month 11 - Do I include my
Forecasted Y value from month 10 as a known Y value

does that mean the formula becomes

Forecast(A11;$B$1:B10;$A$1:A10)

Or should the formula be


Forecast(A11;$B$1:$B$9;$A$1:$A$9)


I'd really apprececiate any help /advice anyone can offer, as I'm really
stuck with this

Regards

Daniel