You are a salesperson and your boss is required to make a table of revenue estimates for the following months based on sales of past months. You are worried about not knowing how to predict next month's sales? Don't worry. After reading the article about how to use TREND - FORECAST function that TipsMake gave below, you can completely solve the boss problem.
1. TREND function
a) Description
- The function returns values in a linear trend in Excel
b) Syntax
= TREND (known_y's, [known_x's], [new_x's], [const])
Inside:
- known_y's: The set of known y values in the relationship y = b * m ^ x, is a required parameter.
+ If known_y's is in a single column or row -> each known_y's column or row is interpreted as a separate variable.
- known_x's: The set of known x values in the relationship y = b * m ^ x, is a required parameter.
+ known_x's may include 1 or more sets of variables.
+ If known_x's is omitted -> it is assumed to be an array of the same size as known_y's
- new_x's: Are the new x values that you want the function to return the values corresponding to the values of y.
+ new_x's must include a column (row) for each independent variable.
+ If new_x's is omitted -> it is assumed to be the same as known_x's.
+ If known_x's and new_x's are omitted -> it is assumed to be the same as known_y's size.
- const: The logical value determining the value of the constant b, is an optional value including the following values:
+ const = True or ignore -> b is calculated normally.
+ const = False or ignore -> b = 1 and m are adjusted such that: y = m ^ x.
Attention:
- The value of the returned formulas has the array formula type -> the function must be entered as an array formula.
- If known_y's ≤ 0 -> the function returns the #NUM! Error value
- When entering values for an argument with the following convention:
+ Use commas to separate values in the same row.
+ Use semicolons separating between rows together.
The following article introduces you to the FORECAST function - one of the functions in the statistical function group is very popular in Excel.
c) Example
Revenue estimates for June, July and August are based on previous months as described in the following data table:
- In the cell to calculate enter the formula: = TREND (D5: D10, C5: C10)
- Press Enter -> return value is:
- Highlight the sales areas in June, July and August (D11: D13 areas) -> press F2:
- Press the key combination Ctrl + Shift + Enter -> estimated revenue for June, July and August is:
2. FORECAST function
a) Description
The function performs a calculation or prediction of a future value by using current values using linear regression.In Excel 2016 this function is replaced by FORECAST.LINEAR function.
b) Syntax
= FORECAST (x, known_y's, known_x's)
Inside:
- x: Data point to predict a value for it, is a required parameter.
- known_y's: Dependent array of an array or range of data.
- known_x's: Independent array of array or range of data.
Attention:
- If x is not a number -> the function returns the #VALUE! Error value.
- If known_y's or known_x's are empty or contain a different number of data points -> the function returns the # N / A error value
- If known_x's = 0 -> variance, the function returns the DIV / 0 error value!
- FORECAST transform equation is: A + BX where:
And x, y are the average samples of AVERAGE (known_x's) and AVERAGE (known y's).
c) Example
Find the predicted value of y and the value of x is 25
- Predict the value of y and the value of x is 25. In the cell to calculate enter the formula: = FORECAST (25, D5: C8, E5: E8)
- Press Enter -> Predict the value of y is: