Excel functions / Statistics

TREND function

Values on the best-fit straight line (least squares): fitted values for the known data, or predictions for new x values. Spills.

Syntax

=TREND(known_y, [known_x], [new_x], [const])

Arguments

When to use it

TREND fits a straight line through your data by least squares, then returns values on that line: the fitted value for every point you have, or forecasts for new x values. It spills the whole result at once.

Leave out new_x to get the trend line for your own data, or give new x values to forecast them. Unlike FORECAST, TREND can also use several x columns at once.

Examples

  1. The trend line through six months

    ABC
    1MonthRevenue
    214242.3
    324544.2
    434446.1
    544947.9
    655249.8
    765051.7
    8
    97
    108

    In C2: =TREND(B2:B7, A2:A7) → 42.3; 44.2; 46.1; 47.9; 49.8; 51.7

    Try this example

  2. Forecast the next two months

    ABC
    1MonthRevenue
    2142
    3245
    4344
    5449
    6552
    7650
    8
    9753.6
    10855.5

    In C9: =TREND(B2:B7, A2:A7, A9:A10) → 53.6; 55.5

    Try this example

  3. Without x values

    ABC
    1MonthRevenue
    214242.3
    324544.2
    434446.1
    544947.9
    655249.8
    765051.7
    8
    97
    108

    In C2: =TREND(B2:B7) → 42.3; 44.2; 46.1; 47.9; 49.8; 51.7

    With no known_x, TREND uses 1, 2, 3 and so on, which suits evenly spaced periods.

    Try this example

Common mistakes

TREND in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try TREND in the playground

Practise it

Related functions