FORECAST Excel Function

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 regressionRegressionRegression Analysis is a statistical approach for evaluating the relationship between 1 dependent variable & 1 or more independent variables. It is widely used in investing & financing sectors to improve the products & services further. read more to predict y values from x values.

 

FORECAST Formula in Excel

FORECAST Formula in Excel

FORECAST formula in excel has three compulsory parameters, i.e., x, known_y’s, known_x’s.

Compulsory Parameters:

  • 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.

Remarks

The FORECAST formula will calculate a new y-value using the simple straight-line equation:

FORECAST Function 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 us understand the working of FORECAST in excel with some examples.

You can download this FORECAST Function Excel Template here – FORECAST Function Excel Template

FORECAST formula in excel can be used as a worksheet function and as a VBA function.

FORECAST Function as Worksheet Function.

Example #1

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.

FORECAST Example 1

Example #2

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 the FORECAST function in excel.

The FORECAST formula in excel to use is: =FORECAST(C43,C23:C42,D23:D42)

FORECAST Example 2

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.

Sub FORECASTfunction()

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

End Sub

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.

FORECAST Example 3

  • 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.

FORECAST Example 4

Recommended Articles

This has been a guide to FORECAST in Excel. Here we discuss the FORECAST Formula and how to use the FORECAST function along with examples and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>