Check Sheet Logo
Check Sheet

TREND

The TREND formula is used to calculate future values based on historical data. It fits a straight line (using the method of least squares) to the arrays specified in the known_data_y and known_data_x parameters and then uses that line to calculate new y-values for the array specified in the new_data_x parameter. If b is set to TRUE, then the calculation will include the y-intercept of the line. This formula is commonly used in forecasting and trend analysis.

Usage

Use the TREND formula with the syntax shown below, it has 1 required parameter and 3 optional parameters:

=TREND(known_data_y, [known_data_x], [new_data_x], [b])
Parameters:
  1. known_data_y (required):
    An array or range containing the known y-values. These are the dependent variable values that you are trying to predict.
  2. known_data_x (optional):
    An optional array or range containing the known x-values. These are the independent variable values that correspond to the known y-values. If omitted, the array [1, 2, ..., n] will be used as the x-values, where n is the number of y-values.
  3. new_data_x (optional):
    An optional array or range containing the new x-values for which you want to predict y-values. If omitted, the function will only return the y-values for the known data x-values.
  4. b (optional):
    An optional boolean value that determines whether to include the y-intercept of the trendline in the calculation. If set to TRUE, the formula will calculate the intercept. If omitted or set to FALSE, the intercept will not be calculated.

Examples

Here are a few example use cases that explain how to use the TREND formula in Google Sheets.

Predicting future sales

If you have historical sales data, you can use the TREND formula to predict future sales based on trends in the data.

Forecasting financials

The TREND formula can be used to forecast financial metrics, such as revenue, expenses, or profit, based on historical data.

Analyzing scientific data

If you have scientific data, you can use the TREND formula to analyze trends and make predictions about future outcomes.

Common Mistakes

TREND not working? Here are some common mistakes people make when using the TREND Google Sheets Formula:

Incorrect range selected for known_data_y

One of the most common mistakes is selecting an incorrect range for the known_data_y argument. Make sure to select only the range that contains the y-values for the known data.

Using an incorrect formula syntax

Make sure to use the correct syntax for the TREND formula. Double-check that you have included all necessary arguments and separated them with commas.

Incorrect range selected for known_data_x

If you are using an x-range for the known_data_x argument, make sure to select only the range that contains the x-values for the known data.

Forgetting to include the new_data_x argument

If you want to use the TREND formula to predict new y-values based on new x-values, make sure to include the new_data_x argument in your formula.

Using non-numeric values for known_data_y or known_data_x

The TREND formula only works with numeric values, so make sure that the ranges you select for known_data_y and known_data_x only contain numbers.

The following functions are similar to TREND or are often used with it in a formula:

  • LINEST

    The LINEST formula is used to calculate the statistics for a line by calculating the line's slope and y-intercept. It is commonly used to find the best-fit straight line for a given set of data points.

  • SLOPE

    The SLOPE formula calculates the slope of the linear regression line that best fits the input data. It is commonly used in statistics to analyze trends and predict future values based on past performance.

  • INTERCEPT

    The INTERCEPT function calculates the point where the line of best fit for a set of data intercepts the y-axis. This function is commonly used in regression analysis to find the constant b in the equation y=mx+b where m is the slope of the regression line.

Learn More

You can learn more about the TREND Google Sheets function on Google Support.