How to Use FORECAST Function in Excel + Tips (Video in Khmer)

Hello everyone! Today I show you step-by-step how to use FORECAST() function in Microsoft Excel. This formula very useful when you want to predict future value based on past history data!

📚 Related Tutorial: Learn more foundational formulas in our guide on Top 15 Basic Excel Formulas for Beginners ↗!

🛠️ What is FORECAST Function?

The FORECAST function help you calculate or estimate future value by looking at old historical data. It use linear regression (straight line trend) to predict new number for you.

Formula Syntax:

=FORECAST(x, known_y's, known_x's)

• x: The target data point (like target month or target year number) you want to find future result for.
• known_y's: Past dependent values range (like past Sales or Revenue amount).
• known_x's: Past independent values range (like past Month numbers or Years).

🛠️ Simple Step-by-Step Example

Imagine you have sales report from Month 1 to Month 5, and you want to predict sales for Month 6:

If you want to estimate sales for Month 6 (cell A7 = 6), type this formula:

=FORECAST(6, B2:B6, A2:A6)

• Result: $2,270 (Excel calculate straight trend and predict Month 6 sales!)

⚠️ Important Errors to Avoid

1. #N/A Error: known_y's and known_x's ranges must have the exact same count of cells! If known_y's has 5 cells but known_x's has 4 cells, Excel returns #N/A.
2. #DIV/0! Error: Happens if variance of known_x's is zero (for example, all numbers in known_x's are identical).
3. #VALUE! Error: Occurs if x or any cell inside known_y's or known_x's contains text instead of numbers.

💡 Easy Pro Tips for FORECAST

1. FORECAST vs FORECAST.LINEAR: In Excel 2016 and newer versions, Microsoft renamed FORECAST to FORECAST.LINEAR. Old FORECAST still works fine for backward compatibility, but FORECAST.LINEAR is recommended for newer Excel!
2. For Seasonal Data, Use FORECAST.ETS: FORECAST assumes data grows in a straight line. If your business has big seasonal peaks (like holiday shopping season), use FORECAST.ETS function instead for better accuracy!
3. Dynamic Cell Reference for target x: Instead of typing number 6 hardcoded inside =FORECAST(6, B2:B6, A2:A6), point to a cell like =FORECAST(A7, B2:B6, A2:A6) so you can easily change target month anytime!