How to use TREND - FORECAST function

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 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:

How to use TREND - FORECAST function Picture 1 How to use TREND - FORECAST function Picture 1

- In the cell to calculate enter the formula: = TREND (D5: D10, C5: C10)

How to use TREND - FORECAST function Picture 2 How to use TREND - FORECAST function Picture 2

- Press Enter -> return value is:

How to use TREND - FORECAST function Picture 3 How to use TREND - FORECAST function Picture 3

- Highlight the sales areas in June, July and August (D11: D13 areas) -> press F2:

How to use TREND - FORECAST function Picture 4 How to use TREND - FORECAST function Picture 4

- Press the key combination Ctrl + Shift + Enter -> estimated revenue for June, July and August is:

How to use TREND - FORECAST function Picture 5 How to use TREND - FORECAST function Picture 5

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).