FORECAST Function in Excel
FORECAST function is categorized under statistical functions in Excel. The FORECAST function is used to calculate or predict the future value of the basis of exiting values. Forecast formula can be used to calculate the statistical value of a forecast made. For example, if we know the past data such as currency flow, we can forecast the future flow using the function.
In mathematical terms, the FORECAST(x, known_y’s,known_x’s) function returns the predicted value of the dependent variable known_ys for the specific value that is x, of the independent variable known_xs by using a best fit linear regression to predict y values from x values.
FORECAST Formula in Excel
FORECAST formula in excel has three compulsory parameters i.e. x, known_y’s, known_x’s.
- x: A numeric x-value for which you want to forecast a new y-value.
- known_y’s: It is an array of the dependent variable or range of data.
- known_x’s: It is the independent array or range of data that is known to us.
The FORECAST formula will calculate a new y-value using the simple straight-line equation:
where and are the sample means and calculated by average (x values) and average(y values).
How to Use FORECAST Function in Excel?
FORECAST function in excel is very simple and easy to use. Let understand the working of FORECAST in excel with some examples.
FORECAST formula in excel can be used as a worksheet function and as a VBA function.
FORECAST Function as Worksheet Function.
In this FORECAST example let’s consider datasets for known y’s values and known x’s values and calculate a forecast value for 30 on the basis of known values x’s and y’s.
=FORECAST(30,B3:B17,C3:C17) output will be 19 as shown in the below table.
Suppose we have the earnings and expenses data from the year 2017 as shown in the below table. Here earning data is considered as known x’s value and expenses data considered as known y’s value. We can use the FORECAST function to predict an additional point along the straight line of best fit through a set of known x- and y-values. Using the data below
Using earnings and expenses data from January 2017 to August 2017, we can predict the expenses for the next upcoming month using the FORECAST function in excel. In this FORECAST example we predict the forecast value for the Sept 2018 month using FORECAST function in excel.
The FORECAST formula in excel to use is: =FORECAST(C43,C23:C42,D23:D42)
We get the results as follow: 1,768
FORECAST Function can be used as a VBA function.
Let’s consider a data set having x values from Range A1:A5 and y values from B1:B5.
Dim xs As Range
Dim ys As Range
Set xs = Sheets(2).Range(“A1:A5”)
Set ys = Sheets(2).Range(“B1:B5”)
Sheets(2).Range(“H1”).Value = Application.worksheetFunction.Forecast(50, ys, xs) // note 50 is a random for testing
Things to Remember
- The length of the known_x’s array should be the same length as the known_y’s, and the variance of the known_x’s must not be zero.
- It will give #N/A! error if:
- The supplied values known_x’s and the supplied known_y’s arrays are different in lengths.
- It gives the #DIV/0! Error when
- Or one or both of the known_x’s or the known_y’s arrays has no value or empty.
- If the variance of the supplied known_x’s is equal to zero.
- If the given future value of x is non-numeric.
This has been a guide to FORECAST in Excel. Here we discuss the FORECAST Formula and how to use FORECAST function along with examples and downloadable excel templates. You may also look at these useful functions in excel –