WorksheetFunction.Forecast how to use it

[Guest]

Hi al

Can anybody explani how I have to use the Forecast function in a proper way

I trie

Range1 = Cells(i, j).Addres
Range2 = Range(Cells(i - 4, j + 1), Cells(i - 1, j + 1)).Addres
Range3 = Range(Cells(i - 4, j), Cells(i - 1, j)).Addres

funct1 = Application.WorksheetFunction.Forecast(Range1, Range2, Range3

but this seems not to wor

What am I doing wrong ???

 

[Mark Rosenkrantz]

PB;

An example.
Mark.

E5:E10 = Month list : 1 to 6
F5:F10 = Saleslist : 1.000,00 to 4.000,00

E12 = 12
E13 = ? = forecasted value. (see formula)

Month Sales
1 1.000,00
2 2.000,00
3 2.500,00
4 3.500,00
5 3.800,00
6 4.000,00

12
? 7.997,14 =FORECAST(E12,F5:F10,E5:E10)

Mark.

 

[Tom Ogilvy]

Dim range1 as Range, range2 as Range, range3 as Range
set Range1 = Cells(i, j)
set Range2 = Range(Cells(i - 4, j + 1), Cells(i - 1, j + 1))
set Range3 = Range(Cells(i - 4, j), Cells(i - 1, j))

funct1 = Application.WorksheetFunction.Forecast(Range1, Range2, Range3)

Value(s) in
Range2 are known Y's
Range3 are known X's
Range1 is value of x for which you are seeking a predicted value of Y.

 

[Guest]

Thank

works fine I also forgot to pu
Sheets(Sheetname).Cells(i, j
so when I tried your solution I was looking in the wrong sheet

I will try to finish my macro no


----- Tom Ogilvy wrote: ----

Dim range1 as Range, range2 as Range, range3 as Rang
set Range1 = Cells(i, j
set Range2 = Range(Cells(i - 4, j + 1), Cells(i - 1, j + 1)
set Range3 = Range(Cells(i - 4, j), Cells(i - 1, j)

funct1 = Application.WorksheetFunction.Forecast(Range1, Range2, Range3

Value(s) i
Range2 are known Y'
Range3 are known X'
Range1 is value of x for which you are seeking a predicted value of Y

 

[Tom Ogilvy]

Dim range1 as Range, range2 as Range, range3 as Range
Dim sheetname as String

sheetname = "Sheet1"

With Sheets(Sheetname)
set Range1 = .Cells(i, j)
set Range2 = .Range(.Cells(i - 4, j + 1), .Cells(i - 1, j + 1))
set Range3 = .Range(.Cells(i - 4, j), .Cells(i - 1, j))
End With
funct1 = Application.WorksheetFunction.Forecast(Range1, Range2, Range3)