The `FORECAST.LINEAR` function in Excel is used to predict a future value along a linear trend. It is particularly useful for forecasting sales, revenues, or any other metric where there is a linear relationship between the independent and dependent variables. Here’s a step-by-step guide on how to use the `FORECAST.LINEAR` function:
Syntax
FORECAST.LINEAR(x, known_y's, known_x's)
- x: The point for which you want to predict a value. This is the independent variable.
- known_y’s: The range or array of dependent data points (the series of existing values you want to use as the baseline for your prediction).
- known_x’s: The range or array of independent data points corresponding to known_y’s.
Steps to Use the Function
- Prepare Your Data: Ensure that you have a set of known x-values (independent variable) and known y-values (dependent variable). The data should be in adjacent columns or rows.
- Select the Cell for the Forecast: Click on the cell where you want the forecasted result to appear.
- Enter the Function:
- Start with an equal sign (`=`) to tell Excel you are entering a formula.
- Type `FORECAST.LINEAR(`.
- Enter the x-value for which you want the forecast. This could be a cell reference or a value typed directly into the formula.
- Provide the range for known y-values.
- Provide the range for known x-values.
- Close the parentheses and press Enter.
Example
Suppose you have sales data over several months and want to forecast sales for a future month. Assume:
- Months are in range `A2:A13`.
- Corresponding sales figures are in range `B2:B13`.
- You want to forecast the sales for month 14.
Your formula would look like:
=FORECAST.LINEAR(14, B2:B13, A2:A13)
This formula predicts the sales figure for month 14 based on the linear trend demonstrated by the months and sales data provided.
Additional Tips
- Ensure that the x-values (known_x’s) and y-values (known_y’s) are the same length. Otherwise, Excel will return an error.
- The function assumes a linear relationship; if your data does not seem linear, consider using other forecasting methods available in Excel such as exponential or moving averages.
By following these steps, you can effectively use the `FORECAST.LINEAR` function to predict future values based on established data trends.
Unlock Your Potential
Excel
Access
Power BI
