Excel FORECAST Function click here to read tutorial
The FORECAST function uses the least squares or linear regression method to forecast future values. For exam
sales, stock requirements, trends etc.
In Excel 2016 this function has been replaced by FORECAST.LINEAR as part of the new library of forecasting f
Syntax: =FORECAST(x, known_y's, known_x's)
x this is the next data point that you want to forecast. E.g. it might be a date, or period number
known_y's the range of cells containing your historical values you're basing your forecast on
known_x's the range of cells containing the periods you already know
Notes:
- All arguments are required and the known y's and x's must contain the same number of data points.
- x must be a numeric value, otherwise the #VALUE! Error will be returned.
- the #DIV/0! Error will be returned if the variance of known_x's equals zero.
Year Month Sales FORECAST
2017 Jan 671,160 Sales Foreca
Feb 635,558
1,000,000
Mar 721,915
Apr 643,960 800,000
May 643,786 600,000
Jun 724,012
Jul 773,626 400,000
Aug 735,758 200,000
Sep 611,256
0
Oct 650,638
42736
42767
42795
42826
42856
42887
42917
42948
42979
Nov 630,391
Dec 754,282 754,282 2017
2018 Jan 694,843
Sales FOR
Feb 696,688
Mar 698,355 Formula in cell E34: =FORECAST(C34,$C$5:$C$16
Apr 700,200
Note that the known_x and known_y ranges are a
May 701,986 relative.
Jun 703,831
cast future values. For example,
new library of forecasting functions.
ate, or period number
ber of data points.
Sales Forecast
42826
42856
42887
42917
42948
42979
43009
43040
43070
43101
43132
43160
43191
43221
43252
2017 2018
Sales FORECAST
ORECAST(C34,$C$5:$C$16,$B$5:$B$16)
and known_y ranges are absolute, but the x argument is
More Resources
Tutorials
Excel Functions......................... https://www.myonlinetraininghub.com/excel-functions
Charting Blog Posts................ http://www.myonlinetraininghub.com/category/excel-charts
Excel Dashboard Blog Posts http://www.myonlinetraininghub.com/category/excel-dashboard
Free Webinars
Excel Dashboards..................... http://www.myonlinetraininghub.com/excel-webinars
Courses
Excel Expert................................ https://www.myonlinetraininghub.com/excel-expert-upgrade
PivotTables................................. https://www.myonlinetraininghub.com/excel-pivottable-course
Support
Excel Forum................................ https://www.myonlinetraininghub.com/excel-forum
Year Month Sales FORECAST
2017 Jan 8,638
Feb 3,735
Mar 28
Apr 96
May 50
Jun 69
Jul 136
Aug 100
Sep 672
Oct 930
Nov 372
Dec 502 502
2018 Jan -1,221
Feb -1,612
Mar -1,964
Apr -2,354
May -2,732
Jun -3,122
