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
Required: Yesarray1
Required: Yesarray2PEARSON
Required: Yesarray1
Required: Yesarray2SLOPE
Required: Yesknown_y
Required: Yesknown_xINTERCEPT
Required: Yesknown_y
Required: Yesknown_xRSQ
Required: Yesknown_y
Required: Yesknown_xSTEYX
Required: Yesknown_y
Required: Yesknown_xFORECAST.LINEAR
Required: Yesx
Required: Yesknown_y
Required: Yesknown_xCOVARIANCE.P
Required: Yesarray1
Required: Yesarray2COVARIANCE.S
Required: Yesarray1
Required: Yesarray2TREND
Required: Yesknown_y
Required: Noknown_x
Required: Nonew_x
Required: NoconstFORECAST
Required: Yesx
Required: Yesknown_y
Required: Yesknown_xGROWTH
Required: Yesknown_y
Required: Noknown_x
Required: Nonew_x
Required: NoconstantLINEST
Required: Yesknown_y
Required: Yesknown_x
Required: Yesconst
Required: YesstatsLOGEST
Required: Yesknown_y
Required: Yesknown_x
Required: Yesconst
Required: Yesstats
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
CORREL | decimal | The correlation of the two lists | CORREL([1, 2, 3, 4], [2, 4, 6, 9]) |
PEARSON | decimal | The Pearson correlation | PEARSON([1, 2, 3, 4], [2, 4, 6, 9]) |
SLOPE | decimal | The slope of the fitted line | SLOPE([2, 4, 6, 9], [1, 2, 3, 4]) |
INTERCEPT | decimal | Where the fitted line crosses the vertical axis | INTERCEPT([2, 4, 6, 9], [1, 2, 3, 4]) |
RSQ | decimal | How much of the variation the line explains, from 0 to 1 | RSQ([2, 4, 6, 9], [1, 2, 3, 4]) |
STEYX | decimal | The standard error of the predicted values | STEYX([2, 4, 6, 9], [1, 2, 3, 4]) |
FORECAST.LINEAR | decimal | The value the fitted line gives at x | FORECAST.LINEAR(5, [2, 4, 6, 9], [1, 2, 3, 4]) |
COVARIANCE.P | decimal | The population covariance | COVARIANCE.P([1, 2, 3, 4], [2, 4, 6, 9]) |
COVARIANCE.S | decimal | The sample covariance | COVARIANCE.S([1, 2, 3, 4], [2, 4, 6, 9]) |
TREND | list of decimals | The line's values at the new points | TREND([2, 4, 6, 9], [1, 2, 3, 4], [5]) |
FORECAST | decimal | The value the fitted line gives at x. The same as FORECAST.LINEAR | FORECAST(6, [10, 12, 15, 17, 20], [1, 2, 3, 4, 5]) |
GROWTH | list of decimals | The values of the fitted growth curve, one for each point asked for | GROWTH([10, 12, 15, 19], [1, 2, 3, 4], [5, 6]) |
LINEST | list of decimals | The 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 squares | LINEST([10, 12, 15, 17], [1, 2, 3, 4], true, false) |
LOGEST | list of decimals | The growth factor and the starting value of the fitted curve, then with stats true the R squared and the standard error | LOGEST([10, 12, 15, 19], [1, 2, 3, 4], true, false) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | An 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.
TRENDreturns a list, one value for each new point. SLOPE,INTERCEPT,RSQandSTEYXtake the values to explain first, then the values that explain them.CORREL,PEARSONand 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
RSQbefore you rely on a forecast. - Keep the lists in the same order, so each pair belongs together.