How to Use Excel FORECAST Function? 2 Easy Ways

• Facebook
• Twitter
• YouTube

Note: This guide on how to use Excel FORECAST function in Excel is suitable for all Excel versions including Office 365)

Imagine a situation where you are given a data set for the past five years and need to forecast or predict an upcoming year’s sales output or expense. 

How would you approach the problem? Are you analytically strong to calculate the value using mathematical equations and formulas?

Good thing Excel has the FORECAST function, which predicts the probable value in the imminent cell using the previous cell range as a reference to calculate the prediction value.

In this article, I will show you how to use Excel FORECAST function, including its syntax, arguments, and benefits with real-time examples.

You’ll learn:

• Universal SYNTAX for a FORECAST Function
• How to Use Excel Forecast Function- Different Methods
  • Entering the Formula Manually
  • By Using the Formula Bar
• Points to Remember

Related Reads:

HOW TO MAKE AN EXCEL FORECAST SHEET IN 3 STEPS – THE BEST GUIDE

BASIC FORECASTING IN EXCEL: RECORDED WEBINAR

HOW TO USE THE ROUND FUNCTION IN EXCEL?

Universal SYNTAX for a FORECAST Function

=FORECAST(x, known_y’s, known_x’s)

The FORECAST function can be divided into three arguments: x– denotes the cell corresponding to the forecast value, known_y’s- denotes the range of cells with dependent data, and known_x’s denotes the range of cells with independent data.

How to Use Excel Forecast Function- Different Methods

In the following steps, let’s look at the different methods for using the Excel Forecast function. You can use the Forecast function by two different methods. The different methods to use this function are:

Entering the Formula Manually

Let’s say you have a six years sales data list and want to predict the sales for the 7th year. In our case, it is 2023. 
Step 1- To use the FORECAST function, choose the cell to enter the formula for the function. Considering the range of cells required to perform the function, enter the following formula =FORECAST(A9, B3:B8, A3:A8)

Step 2- Once you enter the formula in the destination cell, hit Enter. The answer is displayed in the B9 cell.

Suggested Reads,

HOW TO USE THE CEILING FUNCTION IN EXCEL? A STEP-BY-STEP GUIDE

HOW TO USE FLOOR FUNCTION IN EXCEL? 2 EASY WAYS

HOW TO USE CONVERT FUNCTION IN EXCEL? A STEP-BY-STEP GUIDE

By Using the Formula Bar

The sales for the years are listed in the first column. Our goal is to find out the prediction of sales for the year 2016.

Step 1- First, pick the destination cell where you want to apply the FORECAST function. Click on the More Functions drop-down menu under the Formula tab. Select the Statistical option from the list displayed.

Step 2- The statistical option contains important analytical functions such as COVARIANCE, FREQUENCY, and types of FORECAST functions. Scroll down and locate the FORECAST.LINEAR option.

Step 3- Now enter the argument values. In this case, the following values are the arguments considering the data.

• X = A9, 

• known_ys = B3:B8, and

• known_xs = A3:A8.

• Step 4- Double-check the values you entered before clicking OK. The result preview is displayed as (Formula Result) in the bottom left section of the Function Arguments dialog box. 

• You can verify the value, and the formula result entered within the dialog box.

Step 5- The formula is displayed in the destination cell. Finally, press Enter to return the answer to the destination cell.

We saw how to use Excel FORECAST function using the formula bar in this method. While entering the values, you might face potential error prompts without knowing why. Let’s cover that in the next section.

Points to Remember

1. There should be no zero variance for the known_x‘s array. It should have the same length as the known_y‘s array.

2. If you face an #N/A! Error, the possible explanations are
   • You have entered different length values for the known_x‘s and known_y‘s arrays.
   • The known_x‘s or known_y‘s arrays are left empty.

3. You will face a #DIV/0! Error message when you type the value of the known_X argument as zero.

4. You will get a #VALUE!Error if the input value of the ‘X’ argument is non-numerical. Before entering the formula, check if all the values under analysis are numerical and replace the ones that differ.

Frequently Asked Questions

Final Thoughts

To conclude, the FORECAST function helps predict an imminent cell’s value. All you need to do is to understand the syntax, the values to be entered in corresponding boxes, and multiple data sets. 

In this article, we saw how to use the Excel FORECAST function in two easy ways. Upgrade your Excel skills to perform more useful functions that ease the difficulty level during data analysis.

Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.

Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 150+ IT training courses.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!