TREND (Returns values along a linear trend for Statistical Puprpose)

What Does It Do?

This function predicts values based upon three sets of related values.

The prediction is based upon the Linear Trend of the original values.

The function is an array function and must be entered using Ctrl+Shift+Enter.

Syntax

=TREND(KnownYs,KnownXs,RequiredXs,Constant)

The KnownYs is the range of values, such as Sales Figures.

The KnownXs is the intervals used when collecting the data, such as Months.

The RequiredXs is the range for which you want to make the prediction, such as Months.

Formatting

No special formatting is needed.

Example

The following tables were used by a company to predict when they would start to make a profit.

Their bank manager had told the company that unless they could show a profit by the end of the next year, the bank would no longer provide an overdraft facility.

To prove to the bank that, based upon the past years performance, the company would start to make a profit at the end of the next year, the =TREND() function was used.

The historical data for the past year was entered, months 1 to 12.

The months to predict were entered, 13 to 24.

The =TREND() function shows that it will be month 22 before the company make a profit.

How To Enter An Array Formula

Select all the cells where the array is required, such as F41 to F52.

Type the formula such as =TREND(C41:C52,B41:B52,E41:E52), but do not press Enter.

Hold the Ctrl+Shift keys down.

Press Enter to enter the formula as an array.