The TREND function in Excel is a statistical function that can be used to predict a linear trend in a set of data. The function is useful when you have a set of data points and want to predict future values based on the existing data.
The TREND function returns an array of predicted values, which can then be plotted on a chart or used in other calculations.
The syntax for the TREND function
=TREND(known_y’s, [known_x’s], [new_x’s], [const])
- known_y’s: This is the array or range of dependent variable values.
- known_x’s: This is the array or range of independent variable values. This argument is optional.
- new_x’s: This is the array or range of new independent variable values for which you want to predict the corresponding dependent variable values. This argument is optional.
- const: This is a logical value that specifies whether to force the intercept to be zero. This argument is optional and defaults to TRUE.
Example of how to use the TREND function
Suppose you have a set of data points that represent the number of hours of study and the corresponding test scores for a group of students. You want to use this data to predict the test score for a student who studies for 5 hours.
- Enter the data into Excel. In this example, the hours of study are in column A and the test scores are in column B.
- Select a blank cell where you want to display the predicted test score.
- Type the following formula into the cell:
=TREND(B2:B10,A2:A10,5)
This formula will predict the test score for a student who studies for 5 hours.
Here’s a breakdown of the formula:
- B2:B10: This is the range of test scores.
- A2:A10: This is the range of hours of study.
- 5: This is the new value of hours of study for which you want to predict the test score.
Plotting the Predicted Values
You can also plot the predicted values to visualize the trend:
Create a Scatter Plot
Select your data range (e.g., A2). Go to the Insert tab, choose Scatter, and select a scatter plot.
Add TREND Line
Click on the data points in the scatter plot. Go to Chart Tools > Design > Add Chart Element > Trendline. Select Linear Trendline.
This visual representation helps you see the trend line that the TREND function is using to make predictions.
