How to use FORECAST Function in Google Sheets (Best Guide)

how to use FORECAST Function in Google Sheets 14
Key Takeaways: How to use FORECAST Function in Google Sheets

To use the FORECAST Function in Google Sheets

  1. Prepare Your Data.
  2. Choose the Unknown Value.
  3. Write the Formula.
  4. =FORECAST(unknown_x_value, known_y_values, known_x_values).
  5. Press Enter.

Hi. In this article, we will learn how to use the FORECAST function in google sheets.

We will see how to use this function and how it’s helpful to us. Forecast Function in google sheets follows linear regression to forecast or predict the future data based on the given data. We have known x values and based on that the function is capable to forecast or predict the future y values. The x and y can be interchangeably used. We will see practically how this work is useful when working on real-life data such as weather forecasts, business growth forecasts, staff requirement forecasts, etc. We will learn to use this function with the help of some scenarios.

Use Cases of FORECAST Function in Google Sheets

We use the google trends function to find the trend of the data, a similar functionality is the trend line in the google sheets chart that helps us identify the trend generating in our data inside a chart. Similarly, we have the Forecast function which is defined by name, it makes forecasts based on the given data. We need to learn how to use the FORECAST function in google sheets to predict future values and make the appropriate decisions. We will see the syntax below and will also see some real-life use cases where this function is very helpful.

How to use FORECAST Function in Google Sheets

First, let’s see the syntax of the FORECAST function to learn what data we should have to use the FORECAST function

Syntax

=FORECAST (unknown_x_value, known_y_values, known_x_values)

This function takes three parameters. All parameters are mandatory.

We need to have unknown x values, known y values, and known x values to pass in this function.

Now unknown x value is nothing but a cell reference where we want to predict the future values.

Known values are the set of values (arrays)

Let’s see some sample datasets to understand this function properly.

 How to use FORECAST Function in Google Sheets – To Predict Sales

We have a dataset in which we have the monthly sales of the past year. This is our known data, now we need to predict the sales of the first month of the new year. Now, this is the unknown value. In this scenario, we can easily use the FORECAST function to predict the sale values of the first month of the new year. Let’s see how.

Step 1

Sample data

how to use FORECAST Function in Google Sheets 1

Step 2

Make a chart using this data

2.1 Select all data

how to use FORECAST Function in Google Sheets 2

2.2 Go to Insert > Chart

how to use FORECAST Function in Google Sheets 3

2.3 Select any chart (here I am selecting a line chart)

how to use FORECAST Function in Google Sheets 4

2.4 Add a trendline to understand the trend of the data, Customize > Series > Trendline (tick the checkbox)

how to use FORECAST Function in Google Sheets 5

how to use FORECAST Function in Google Sheets 6

how to use FORECAST Function in Google Sheets 7

2.5 Chart is ready

how to use FORECAST Function in Google Sheets 8

Step 3

Now start writing the FORECAST function in the cell where you want to get the value

how to use FORECAST Function in Google Sheets 9

Step 4

Pass the first parameter (unknown_x_value)

how to use FORECAST Function in Google Sheets 10

Step 5

Pass the second parameter (known_y_values)

how to use FORECAST Function in Google Sheets 11

Step 6

Pass the third parameter (known_x_values)

how to use FORECAST Function in Google Sheets 12

Step 7

Hit Enter key and you’re done.

how to use FORECAST Function in Google Sheets 13

how to use FORECAST Function in Google Sheets 14

Note: The x and y values are based on the chart. We call “x” because it’s on the x-axis in the chart and “y” after all it’s on the y-axis of the chart.

This is how you can simply and easily predict a value based on the known values.

How to use FORECAST Function in Google Sheets – To Predict Profit

This is another use case to learn how to use the FORECAST function in google sheets, in this section we have a dataset in which we have 9 days of a new business and the profit earned in those 9 days, we have to predict the profit value of 10, 11, and 12 days using the FORECAST function.

Step 1

Sample data

how to use FORECAST Function in Google Sheets 15

Step 2

Write the FORECAST function in the first empty cell (day 10)

how to use FORECAST Function in Google Sheets 16

Step 3

Pass the first parameter (unknown_x_value)

how to use FORECAST Function in Google Sheets 17

Step 4

Pass the second parameter (known_y_values)

how to use FORECAST Function in Google Sheets 18

Step 5

Pass the third parameter (known_x_values)

how to use FORECAST Function in Google Sheets 19

Note: Use relative references because we need to drag this formula and we want to cells to be changed when we copy it to the below cell.

Step 6

Hit Enter and you’ve got the future value.
how to use FORECAST Function in Google Sheets 20

Step 7

Drag down to day 11 and day 12

how to use FORECAST Function in Google Sheets 21

Step 8

You’re done. You’ve got the value of profit for days 10,11, and 12.

how to use FORECAST Function in Google Sheets 22

This is how you can solve many problems using the forecast function even with one value you can predict the rest of the 11 values for each month’s profit or cell.

How to use FORECAST Function in Google Sheets – Weather Forecast

In this section, we will learn how to use the FORECAST Function in google sheets to make a weather forecast. For this example, I have a sample dataset in which I have random days of august written in column A and their temperature in Celsius written in column B. The last day’s temperature is missing, so we will find out the temperature of the last day, and then we will use an IF statement to see if the rain is expected or not in column C.

Step 1

Sample data

how to use FORECAST Function in Google Sheets 23

Step 2

Write the forecast formula in the last cell of column B

how to use FORECAST Function in Google Sheets 24

Step 3

Pass the arguments

how to use FORECAST Function in Google Sheets 25

Step 4

Hit Enter and you will get the temperature of that day

how to use FORECAST Function in Google Sheets 26

Step 5

Now in column C, write the IF condition

how to use FORECAST Function in Google Sheets 27

We will check the temperature, if the temperature is less than 13 then it will rain, else it will not rain.

Step 6

Press Enter key and you’re done.

how to use FORECAST Function in Google Sheets 28

how to use FORECAST Function in Google Sheets 29

This is how simply you can use the FORECAST function along with the IF statement to make a weather forecast.

 How to use FORECAST Function in Google Sheets – Predict Multiple Values

In this section, we will see how to use the forecast function in google sheets to predict multiple values using a single formula. Of course, we may need to find out 10 values based on 100 known values, so we will surely not write the formula 10 times. Instead, we will use an ARRAYFORMULA along with the FORECAST Function.

I have a simple dataset here on which we will apply the combination of ARRAYFORMULA and FORECAST.

Step 1

Sample data

how to use FORECAST Function in Google Sheets 30

Step 2

In the first empty cell write the ARRAYFORMULA

how to use FORECAST Function in Google Sheets 31

Step 3

Now, inside ARRAYFORMULA, write the FORECAST Function

how to use FORECAST Function in Google Sheets 32

Step 4

Pass the appropriate arrays

how to use FORECAST Function in Google Sheets 33

Step 5

Press Enter key, and you have got all the values together

how to use FORECAST Function in Google Sheets 34

This is how simply you can use the forecast function in google sheets to predict multiple values

Download/Copy Google Sheets Practice Workbook

Important Notes on FORECAST Function in Google Sheets

  • Before applying the FORECAST function to data, you must check that there should be no outlier of the data.
  • The known x values should be numbers only, otherwise, you can see an NA error
  • The variance among the values of x should be 0, or the function can return a DIV error.
  • The values should have an equal number of rows, otherwise, it will return an NA error.
  • Any text encountered in the values of the x-axis arguments will be ignored, and only numbers will be taken.

Frequently Asked Questions

How to use the Forecast function in google sheets?

Simply you need to have three values, one is the unknown-x-value, then the known-y-values, and then the known-x-values. You need to pass these values into the function, and the FORECAST function will predict the resultant value according to your array.

Why should I use the forecast function?

You can forecast function to find out the future values, you have the record of your business sales of last year and now you want to predict the sale value for the next year’s first month. The forecast function is capable enough to do this task for you following the linear regression.

How can I use the forecast function to predict multiple values?

You can use the forecast function to predict multiple values by using it along with the ARRAYFORMULA. It will enable you to pass an array instead of a value as the first argument and the complete array of the unknown values will be predicted by the function.

How to avoid errors when working with Forecast Function?

The variance among the values of x should be 0, or the function can return a DIV error. You should have realistic data to get an appropriate result, if the data has huge differences and does not follow a trendline then it may cause an unwanted result or even a very unobvious result from the forecast function

Conclusion

Wrapping up the entire discussion here on how to use the FORECAST function in google sheets. We have learned the forecast function from very basics, we have seen the syntax in detail, and also used a chart to demonstrate the x and y axis, which may be confusing for beginners. I have discussed four use cases and implemented them to make you understand the usage of the forecast function and the capabilities of this function when used with the ARRAYFORMULA. I tried to cover everything related to this function, and we saw some real-world applications of this function in the context of google sheets. We have tried to use simple data sets and charts to let you learn by the practice this function yourself. I have left a link to the template spreadsheet file used in this article above for your reference. Make a copy and play around for a better and practical understanding of the FORECAST function in google sheets.

That’s all from how to use the FORECAST function in google sheets. I will see you soon, take care, and have a nice day. Thank you, keep learning with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general