In this lesson you can learn how to use forecast function and how to... predict future.

Thanks to forecast function Excel can read the future. It is not a joke! Excel can predict some values if you give past data.

You can use Forecast function to predict simple data and also to make advanced analysis for your company. Let's see how forecast function works and how to use it.

Definition of FREQUENCY function

Forecast function looks like this:

• x is the argument for which you are looking for some value 
• known y's are historical values, which you know 
• known x's are historical arguments, which you know

It can looks difficult, but it is not. Let's see some examples to explain more.

Example 1 How long students are studying to the test?

You have some historical data about test equals. You also know, how long students have studying to the test.

You want to calculate, how many point will get the student, who have been studying for 8 hours. You use forecast function to calculate that.

x equals 8, because you are looking for a value for 8 hors argument

known y's are test points, which you already know

known x's are hours studyng, which you also know

Formula in this situation is =FORECAST(B11,C3:C10,B3:B10) like on the picture below:

Forecasted value is 77.

Value 77 means, that based on historical values, student will get 77 points. I hope forecast funtion is easy and clear. Lets do one more example.

Example 2 Forecasting net income

You have sales and net income of some company.

You want to calculate net income for 500 000$ sales.

x is 500 000$

known y's is historical net income

known x's is historical sales

Formula here is =FORECAST(B11,C3:C10,B3:B10)

Net income equals 11921 $.

Example 3 Dates forecasting

You can also forecast dates. Here’s an example.

In this table you have data of sales in the end of each month. You want to know where you will have 1 000 000 $ of sales.

x is 1 000 000$

known y's is historical dates

known x's is historical sales

Formula is: =FORECAST(B10;C3:C9;B3:B9)

Formatting of cells is General by default so your result is not a date.

Change formatting of the cell. Click right and next Format Cells... and change formatting to Date.

Result is 2011-11-03. Excel predicts that your company will have 1 million USD sales at that day.

There are some basic examples. Of course there are also many ways to use forecast function for business. Forecast function is easy to use and really powerful.

Tip in case of errors:

If the x argument  is nonnumeric, FORECAST function returns the #VALUE! error.

If known_y's and known_x's are empty or contain a different number of data points, FORECAST function returns the #N/A error.

If the variance of known_x argument is zero, the function FORECAST returns the #DIV/0! error.