You can use the CORREL function to determine the degree of linear relationship between two sets of data.
To use the FORECAST function, you must have a set of X-Y data pairs. Then you provide a new X value, and the function returns the Y value that would be associated with that X value based on the known data. The function takes three arguments:- The first argument is the X value for which you want a forecast.
- The second argument is a range containing the known Y values.
- The third argument is a range containing the known X values.
Don’t use Excel’s FORECAST function with data that isn’t linear. Doing so produces inaccurate results.
Now you can work through an example of using Excel’s FORECAST function to make a prediction. Imagine that you’re the sales manager at a large corporation. You’ve noticed that the yearly sales results for each of your salespeople is related to the number of years of experience each has. You’ve hired a new salesperson with 16 years of experience. How much in sales can you expect this person to make?The image below shows the existing data for salespeople — their years of experience and annual sales last year. This worksheet also contains a scatter chart of the data to show that it’s linear. It’s clear that the data points fall fairly well along a straight line. Follow these steps to create the prediction using Excel’s FORECAST function:
- In a blank cell, type =FORECAST( to start the function entry.
The blank cell is C24.
Forecasting sales with Excel's FORECAST function. - Type 16, the X value for which you want a prediction.
- Type a comma (,).
- Drag the mouse over the Y range or enter the cell range.
C3:C17 is the cell range in the example.
- Type a comma (,).
- Drag the mouse over the X range or enter the cell range.
B3:B17 is the cell range in the example.
- Type a ) and press Enter to complete the formula.
After you format the cell as Currency, the result displays the prediction that your new salesperson will make $27,093 in sales his first year. But remember: This is just a prediction, not a guarantee!
Find out how to use the Excel 2019 Forecast Sheet feature.