Regression functions

See how two lists move together and project the next value.

Syntax

Write the function's name and the lists. Lists of the same length are paired in order.

CORREL(array1, array2)
PEARSON(array1, array2)
SLOPE(known_y, known_x)
INTERCEPT(known_y, known_x)
RSQ(known_y, known_x)
STEYX(known_y, known_x)
FORECAST.LINEAR(x, known_y, known_x)
COVARIANCE.P(array1, array2)
COVARIANCE.S(array1, array2)
TREND(known_y, [known_x], [new_x], [const])
FORECAST(x, known_y, known_x)
GROWTH(known_y, [known_x], [new_x], [constant])
LINEST(known_y, known_x, const, stats)
LOGEST(known_y, known_x, const, stats)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • CORREL

    array1

    Required: Yes
  • array2

    Required: Yes
  • PEARSON

    array1

    Required: Yes
  • array2

    Required: Yes
  • SLOPE

    known_y

    Required: Yes
  • known_x

    Required: Yes
  • INTERCEPT

    known_y

    Required: Yes
  • known_x

    Required: Yes
  • RSQ

    known_y

    Required: Yes
  • known_x

    Required: Yes
  • STEYX

    known_y

    Required: Yes
  • known_x

    Required: Yes
  • FORECAST.LINEAR

    x

    Required: Yes
  • known_y

    Required: Yes
  • known_x

    Required: Yes
  • COVARIANCE.P

    array1

    Required: Yes
  • array2

    Required: Yes
  • COVARIANCE.S

    array1

    Required: Yes
  • array2

    Required: Yes
  • TREND

    known_y

    Required: Yes
  • known_x

    Required: No
  • new_x

    Required: No
  • const

    Required: No
  • FORECAST

    x

    Required: Yes
  • known_y

    Required: Yes
  • known_x

    Required: Yes
  • GROWTH

    known_y

    Required: Yes
  • known_x

    Required: No
  • new_x

    Required: No
  • constant

    Required: No
  • LINEST

    known_y

    Required: Yes
  • known_x

    Required: Yes
  • const

    Required: Yes
  • stats

    Required: Yes
  • LOGEST

    known_y

    Required: Yes
  • known_x

    Required: Yes
  • const

    Required: Yes
  • stats

    Required: Yes

Examples

Five weeks of advertising spend against the sales that followed, and how closely the two travel together.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
CORRELdecimalThe correlation of the two listsCORREL([1, 2, 3, 4], [2, 4, 6, 9])
PEARSONdecimalThe Pearson correlationPEARSON([1, 2, 3, 4], [2, 4, 6, 9])
SLOPEdecimalThe slope of the fitted lineSLOPE([2, 4, 6, 9], [1, 2, 3, 4])
INTERCEPTdecimalWhere the fitted line crosses the vertical axisINTERCEPT([2, 4, 6, 9], [1, 2, 3, 4])
RSQdecimalHow much of the variation the line explains, from 0 to 1RSQ([2, 4, 6, 9], [1, 2, 3, 4])
STEYXdecimalThe standard error of the predicted valuesSTEYX([2, 4, 6, 9], [1, 2, 3, 4])
FORECAST.LINEARdecimalThe value the fitted line gives at xFORECAST.LINEAR(5, [2, 4, 6, 9], [1, 2, 3, 4])
COVARIANCE.PdecimalThe population covarianceCOVARIANCE.P([1, 2, 3, 4], [2, 4, 6, 9])
COVARIANCE.SdecimalThe sample covarianceCOVARIANCE.S([1, 2, 3, 4], [2, 4, 6, 9])
TRENDlist of decimalsThe line's values at the new pointsTREND([2, 4, 6, 9], [1, 2, 3, 4], [5])
FORECASTdecimalThe value the fitted line gives at x. The same as FORECAST.LINEARFORECAST(6, [10, 12, 15, 17, 20], [1, 2, 3, 4, 5])
GROWTHlist of decimalsThe values of the fitted growth curve, one for each point asked forGROWTH([10, 12, 15, 19], [1, 2, 3, 4], [5, 6])
LINESTlist of decimalsThe slope and the intercept of the fitted line, in that order. With stats true, a grid of 5 rows of 2: the slope and intercept; their standard errors; R squared and the standard error of y; F and the degrees of freedom; the regression and residual sums of squaresLINEST([10, 12, 15, 17], [1, 2, 3, 4], true, false)
LOGESTlist of decimalsThe growth factor and the starting value of the fitted curve, then with stats true the R squared and the standard errorLOGEST([10, 12, 15, 19], [1, 2, 3, 4], true, false)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001An argument is not a list of numbers, the lists differ in length ("Data sets must have the same length."), or every value of the explaining list is the same (SLOPE: division by zero)Pass lists of numbers of equal length that vary
Limitations
  • The two lists must be the same length.
  • These functions fit a straight line. TREND returns a list, one value for each new point.
  • SLOPE, INTERCEPT, RSQ and STEYX take the values to explain first, then the values that explain them. CORREL, PEARSON and the covariances are symmetrical, so their order does not matter.
  • A list of one value is not refused by CORREL: it answers 0. Pass at least two pairs.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Check RSQ before you rely on a forecast.
  • Keep the lists in the same order, so each pair belongs together.