Site icon Best Excel Tutorial

How to use TREND function

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

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.

  1. Enter the data into Excel. In this example, the hours of study are in column A and the test scores are in column B.
  2. Select a blank cell where you want to display the predicted test score.
  3. 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:

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.

Exit mobile version