Buzz
This article introduces you to the TREND function - one of the widely used statistical functions in Excel.
Description: Returns values along a linear trend.
Syntax: TREND(known_y's, [known_x's], [new_x's], [const])
In which:
- known_y's: Set of known y values in the relationship y = bm^x, is the mandatory parameter.
+ If known_y's lies in a column or a single row -> each column or row of known_y's is understood as a separate variable.
- known_x's: Set of known x values in the relationship y = bm^x, is the mandatory parameter.
+ known_x's may include one or multiple 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 for which you want the function to return corresponding y values.
+ new_x's must include one column (row) for each independent variable.
+ If new_x's is omitted -> it is assumed to be the same as known_x's.
+ If both known_x's and new_x's are omitted -> it is assumed to be the same size as known_y's.
- const: A logical value determining the value of the constant b, is an optional value consisting of the following values:
+ const = True or omitted -> b is computed normally.
+ const = False or omitted -> b = 1 and m is adjusted such that: y = m^x.
Note:
- The values returned by the formulas are array formulas -> the function must be entered as an array formula.
- If known_y's ≤ 0 -> the function returns an error value #NUM!
- When entering values for an argument with the following conventions:
+ Use commas to separate values within the same row.
+ Use semicolons to separate rows from each other.
Example:
Predicting revenue for June, July, August based on previous months as described in the data table below:
- In the cell where you want to calculate, enter the formula: =TREND(D6:D10,C6:C10)
- Press Enter -> the returned value is:
- Select the revenue range for June, July, August (range D11:D13) -> press F2:
- Press the shortcut keys Ctrl +Shift + Enter -> the predicted revenue for months 6, 7, 8 is:
Above are instructions and some specific examples when using the TREND function in Excel.
Wishing you all success!
