How to Use Excel FORECAST Function? 2 Easy Ways
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
- 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
- There should be no zero variance for the known_x‘s array. It should have the same length as the known_y‘s array.
- 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
What is the use of the Excel FORECAST function?
An Excel FORECAST function predicts the upcoming value of an existing group of values in a selected column. The FORECAST function is beneficial in predicting the expected sales value for the upcoming year, or annual imports and exports of products, etc.
Can I use the FORECAST function for non-numeric data?
You cannot use the FORECAST function for non-numerical values. First, convert all non-numerical values to numerical ones before using the function to predict it successfully without displaying an error message.
How true is the Excel FORECAST function?
It depends on the value you enter in the column under evaluation. If you are going to assume and enter values in the cell range arbitrarily, then you cannot expect a true possible prediction outcome. However, Excel assures 95% confidence in the prediction results using the FORECAST function.
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!