EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel FORECAST Formula in Excel
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

FORECAST Formula in Excel

By Arun GuptaArun Gupta

forcast formula in excel

Forecast Formula in Excel (Table of Content)

  • Introduction to Forecast Formula in Excel
  • How to Use Forecast Formula in Excel

Introduction to Forecast Formula in Excel

The forecast formula is used to predict or calculate a future value which is based on the past data in financial modeling. It uses linear regression to predict the value. This is one of the Statistical in-built Function. It can be used as a worksheet function as well in a formula with other function. The user can use this function to calculate future growth, sales, etc. In Excel, 2013 and earlier versions have the Forecast function, which is now changed to FORECAST.LINEAR function. The forecast formula is still available in Excel 2016 and the latest version with backward compatibility.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

Syntax:

FORECAST () – It will return future value which is based on the past data. There are three parameters – (x, known_y’s and known_x’s).

forecast syntax

An argument in the FORECAST Function

x – It is a mandatory parameter for which the user wants to predict the value.

known_y’s – It is an optional parameter; it is the data range or array of dependent data which is known.

known_x’s –  It’s an optional parameter; it is the data range or array of independent data known.

Equation for Forecast: “y=a + bx “

where: x and y are the samples means AVERAGE (known_x’s) and AVERAGE (known y’s).

How to Use Forecast Formula in Excel?

Excel Forecast Formula is very simple and easy. Let’s understand how to use the Excel Forecast Formula with few examples.

You can download this FORECAST Formula Excel Template here – FORECAST Formula Excel Template

Example #1 – Basic Forecast Formula in Excel

There are some known Y and known X value, so a user wants to calculate the known Y 30 value for known X based on past data, which is known Y and known X. Let’s see how the Forecast Function can solve this problem.

Open MS Excel, go to Sheet1 where the user wants to calculate a Forecast value for 25.

FORECAST Formula in Excel example 1-1

Create one header for the Forecast result to show the function result in cell A11.

FORECAST Formula in Excel example 1-2

Click on cell B11 and apply Forecast Formula.

FORECAST Formula in Excel example 1-3

Now it will ask for x, which is the user wants to forecast the value; select cell A10.

FORECAST Formula in Excel example 1-4

Now it will ask for a known, which user already have in column A, select cell A2 to A9.

FORECAST Formula in Excel example 1-5

Now it will ask for a known, which user already have in column B, Select cell B2 to B9.

FORECAST Formula in Excel example 1-6

Press Enter key.

FORECAST Formula in Excel example 1-7

Summary of Example 1: As the user wants to Forecast the value of 25, which is coming after calculation 10.46332128 based on all given data. Which is available in cell B11 as the Forecast result.

Example #2 – Forecast Formula for Company Future Expenses

There is company data for their earnings and expenses data from the year 2004 to 2018, as shown in the below table, so a user wants to calculate the expense value in the year 2020. Let’s see how the Forecast Formula can solve this problem.

Open MS Excel, go to Sheet2, where the user wants to calculate the expense value for the year 2020.

forecast example 2-1        

Create one header for the Forecast result to show the function result in cell A11 and merge it to cell B11.

forecast example 2-2

Click on cell C11 and apply Forecast Formula.

forecast example 2-3

It will then ask for x, which is the user wants to forecast the value and select cell B10.

forecast example 2-4

Now it will ask for known, which user already have in column B, select cell B2 to B9.

forecast example 2-5

Now it will ask for known, which user already have in column B, select cell B2 to B9, write the formula in cell C11.

forecast example 2-6

Press Enter key.

forecast example 2-7

Summary of Example 2: As the user wants to Forecast the expense value in the year 2020, which is coming after calculation 33333.3333 based on all given data. Which is available in cell C11 as the Forecast result.

Example #3 – Data Calculation and Comparing with the Forecast.Linear Formula

Data Calculation for park visitor in ABC park and comparing with the Forecast.Linear Formula. There is an ABC Park where some data are given like park visitor from the year 2014 to 2018 as shown in the below table, so a user wants to calculate the next 3 years park visitor number each year.

Let’s see how the Forecast Formula can solve this problem. Open MS Excel, Go to Sheet3 where the user wants to calculate the next 3 years park visitor number in each year.

forecast example 3-1        

Create one header for the Forecast result to show the function result in cell D5 and merge it to cell D6.

FORECAST example 3-2  

Click on cell D7 and apply Forecast Formula.

forcast example 3-3

It will then ask for x, which is the user wants to forecast the linear value and apply the formula in cell E7.

forcast example 3-4

Press Enter key.

 FORECAST example 3-5

Summary of Example 3: As the user wants to calculate the next 3 years park visitor number in each year 2019 onwards, which is available in the D and E column as the FORECAST result.

Things to Remember

  • The FORECAST function will return a numeric value based on provided known data known_ys and known_x’s. The length of known_y’s and known_x’s must be the same.
  • If x is a no-numeric value, then the FORECAST formula will return an #VALUE!
  • If there is empty or non-numeric or zero in the data known_y’s and known_x’s, then the FORECAST formula will return #DIV/0!

Recommended Articles

This is a guide to Forecast Formula in Excel. Here we discuss how to use Forecast Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. INDIRECT Formula in Excel
  2. RIGHT Formula in Excel
  3. Excel Forecast Function
  4. MAX Formula in Excel
Popular Course in this category
MS Excel Training Bundle
  13 Online Courses |  100+ Hours |  Verifiable Certificates |  Lifetime Validity
4.5
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download FORECAST Formula Excel Template

EDUCBA

Download FORECAST Formula Excel Template

EDUCBA

डाउनलोड FORECAST Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more