How to use the TREND function
What is the TREND function?
The TREND function calculates values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. It returns the y-values along that line for the array of new_x's that you specify.
In Excel versions prior to 365 the TREND function returns an array of values and must be entered as an array formula.
What is line fitting?
Line fitting is the process of fitting a straight line model to data in order to describe the linear relationship between two variables. The resulting line is called a regression line. The line of best fit minimizes the distance from all data points and allows predicting one variable from the other.
What is the method o least squares?
The method of least squares is a standard approach in regression analysis to find the best-fitting line or curve to a set of data points by minimizing the sum of the squares of the residuals.
What are residuals?
Residuals are the deviations of points from the fitted line/curve. Summing the squared residuals across all data points gives a measure of total error. The line/curve with the least sum of squared residuals provides the best fit. The resulting model fits the data well while being less sensitive to outliers.
What is a linear equation?
A linear equation is a type of equation that can be written in the form ax + b = 0, where a and b are constants and x is a variable. A linear equation represents a relationship between two quantities that are proportional to each other.
For example, if you have a linear equation that says y = 3x + 4, it means that for every unit increase in x, the value of y increases by 3 units and when x is zero y is 4.
The graph of a linear equation is always a straight line, a linear equation does not involve powers of variables.
How to enter an array formula?
An array formula is a formula that returns multiple values. In versions earlier than Excel 365 you have to follow these steps:
- Select the cell range that fits the output.
- Type or copy/paste the array formula in the formula bar.
- Press and hold CTRL and SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
The array formula in the formula bar changes so it also shows beginning and ending curly brackets like this: {=array_formula}
The curly brackets tell you that you successfully enter a formula as an array formula. The downside is that array formulas are easily broken when you edit the formula, you need to remember to repeat the steps above if you change the formula.
The good news is that Excel 365 simplified things a lot, you now enter regular and array formulas the same way simply by pressing the Enter key. Array formulas are now called dynamic array formulas meaning they automatically spill values to cells below and to the right as far as needed.
What is the difference between the TREND function and the FORECAST.LINEAR?
The FORECAST.LINEAR calculates a single value where as the TREND function calculates multiple values. You are required to enter the TREND function as an array formula if you use an Excel version prior to Excel 365.
TREND function Syntax
TREND(known_y's, [known_x's], [new_x's], [const])
TREND function Arguments
| nown_y's | Required. The set of y-values you already know in the relationship y = mx + b. |
| [known_x's] | Optional. An optional set of x-values that you may already know in the relationship y = mx + b. Default values are 1, 2, 3 ... |
| [new_x's] | Optional. New x-values for which you want TREND to return corresponding y-values. If you omit new_x's, it is assumed to be the same as known_x's. |
| Â [const] | Optional. A logical value specifying whether to force the constant b to equal 0. |
TREND function example
Array formula in cell D8:D11
TREND function not working
How is the TREND function calculated?
The linear equation:Â y = mx + b
y - dependent variable
x - independent variable
m - slope
b - where the function intercepts the y axis
m = Σ(x - x̄)(y - ȳ)/Σ(x - x̄)2
Σ - sum of
x̄ - sample mean
ȳ - sample mean
b = ȳ - mx̄
Functions in 'Statistical' category
The TREND function function is one of many functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form