**Formula of Excel Forecast (Table of Contents)**

## Forecast Formula in Excel

Forecast formula in excel is a statistical formula to predict the future values based on any given set of data. This forecast function is based on X and Y axis and is similar to linear regression done. It uses two values from the X axis and Y axis and predicts a value for Y.

The syntax for forecast formula is,

Where in this function X is the point where we want to predict the values, Known X and Known Y are the set of data available with us to work with?

- Forecast formula is based on Linear Trendline. For example, we have data for the past three years for sales done by some products and we want to predict what will be the sales done by the same product in the next year. This can be done by using the forecast formula in excel.
- The way we use this formula in above example will be X will be the coming next year for us and the known Y is the sales done by the product while the known X is the years we have.
- The forecast function works on linear regression, which means it is based on the equation of a straight line. Which is y = mx + c.

We will look deeper into the forecast formula by using some examples in Excel.

### Examples of Forecast Formula

Let’s see some simple examples of Forecast Formula in excel to understand it better.

#### Example #1

Let us use the example described earlier for calculating the amount of sales done by some specific product in the coming year. Consider the following data,

4.9 (1,353 ratings)

We have data for the last three years of sales done by the product and now we want to find the sales will be done next year.

- IN cell B5 write the following formula,

- Select the known X which is 2019 in this case,

- Now select the known Y’s which is the sales done by the product in previous years,

- After that select the know X we have which is the previous year,

- Close the parenthesis and press enter to see the result,

So as per forecast formula, the product will do a sales of 20000 in the year 2019.

#### Example #2

Let’s use another set of data to forecast. For examples, we have data for elections. We have the number of voters available and amount of votes polled. We can predict what will the votes polled for a certain number of increase or decrease in the number of voters in the city. Consider the following data,

We will try to find out that if the number of voters is 10500 for the year 2019 how many votes can be polled in that year.

- In cell C6 write the following formula,

- For the x value select the value in cell B6 as for that specific value we want to predict the number of votes,

- Now select the values for known Y’s which is the number of votes polled,

- For the known X which is the number of voters we have, select the data in column B from B2:B5,

- Close the parenthesis and press enter to see the result.

If we round off the value we can see that for 10500 voters 6686 votes will be polled in the year 2019.

#### Example #3

In this example, we have a growth component for any organization for the last three years. We want to predict what will be the growth percentage for the next year. We have data for the last three years of growth percentage. Consider the below data,

- In Cell B5 write the following formula,

- For known X select the value for which we want to predict the value,

- For known Y select the data for the growth percentage we already have,

- For known X select the data in column A from A2: A4,

- Close the parenthesis and press enter to see the result,

For the year 2019, the growth percentage will be 8%.

### Things to Remember

- The value in X and Y should be an integer value.
- The Formula predicts future values based on the data provided.
- The data in known X and known Y value which are used for prediction must be of the same length.

### Recommended Articles

This has been a guide to Forecast Formula in Excel. Here we discuss how to use Forecast formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

## Leave a Reply