Forecasting with Excel
I applied for Data analyst role dealing with Vaccine data. The technical challenge which I received was surprisingly asking me to use Excel and do projection for next year. Although I never used but was easy to learn and implement. That was FORECAST function which returns a prediction of a future value based on existing values provided. Basically it is using Linear regression for calculation.
The FORECAST function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function in Excel. As a worksheet function, the FORECAST function can be entered as part of a formula in a cell of a worksheet.
The syntax for the FORECAST function in Microsoft Excel is:
FORECAST( x-value, known_y_values, known_x_values )Parameters
x-valueThe x-value used to predict the y-value.known_y_valuesThe known y-values used to predict the y-value.known_x_valuesThe known x-values used to predict the y-value.
Returns
The FORECAST function returns a numeric value.
If x-value is not a numeric value, the FORECAST function will return the #VALUE! error.
If known_y_values and known_x_values contain different numbers of elements, the FORECAST function will return the #N/A error.
Type of Function
- Worksheet function (WS)
Example (as Worksheet Function)
Let’s look at some Excel FORECAST function examples and explore how to use the FORECAST function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following FORECAST examples would return:
=FORECAST(5, B2:B6, A2:A6)
Result: 11.8937852=FORECAST(10, B2:B6, A2:A6)
Result: 20.03269866=FORECAST(8, {1,2,3}, {4,5,6})
Result: 5=FORECAST(7, {5.8, -1}, {2, -5})
Result: 10.65714286=FORECAST(50, {-1,-2,-3,-4}, {10,20,30,40})
Result: -5
Hope you find this article helpful and please reach me @kristinelpetrosyan@gmail.com for any questions you may have.
