How does the Forecast function work

[aki096]

Hi,

How does the forecast function actually work (not the syntax)? Like how is it calculated? the reason I'm asking is because I need to implement something like the forecast function in Access and I need to know how it is calculated.


Thanks

[martindwilson]

http://exceltips.vitalnews.com/Pages..._Function.html

[shg]

It's a linear regression -- the exact algorithm is shown in Help. If you wanted reassurance, you could do it in formulas and verify:

       -A- ---B--- C -D-- ---E--- -----------------------------------F-----------------------------------
   1    x     y                   Formulas                                                               
   2    1   10.27                                                                                        
   3    2   20.14    xAvg   5.50  E3: = AVERAGE(x)                                                       
   4    3   30.12    yAvg  55.45  E4: = AVERAGE(y)                                                       
   5    4   40.73       b  10.02  E5: = SUMPRODUCT( (x - xAvg) * (y - yAvg) ) / SUMPRODUCT( (x - xAvg)^2)
   6    5   50.63       a   0.34  E6: = yAvg - b * xAvg                                                  
   7    6   60.93                                                                                        
   8    7   70.82                                                                                        
   9    8   80.25                                                                                        
  10    9   90.11                                                                                        
  11   10  100.50                                                                                        
  12                  14  140.61  E12: = a + b * D12                                                     
  13                  14  140.61  E13: = FORECAST(D13, y, x)

[ExcelIsEasy]

Here's the short tutorial about forecast function: Forecast function