Syntax
=LINEST(known_ys, [known_xs], [const], [stats])Arguments
| Argument | Required | Description |
|---|---|---|
known_ys | Required | The dependent values |
known_xs | Optional | The independent values, one column per variable (1, 2, 3... when left out) |
const | Optional | TRUE or left out to calculate the intercept, FALSE to force it to 0 |
stats | Optional | TRUE to also return the regression statistics |
How to use LINEST
When LINEST returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
LINEST takes 1 required argument and 3 optional arguments. Type =LINEST( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=LINEST({1;9;5;7}, {0;4;2;3})Result Spilled result
| A | B | |
|---|---|---|
| 1 | 2 | 1 |
=LINEST({3;5;7}, {1;2;3}, FALSE)Result Spilled result
| A | B | |
|---|---|---|
| 1 | 2.428571429 | 0 |
Related statistical functions
- HYPGEOM.DISTReturns the hypergeometric distribution: the probability of a number of successes in a sample drawn without replacement.
- INTERCEPTReturns where the best fit straight line crosses the y axis.
- KURTReturns the kurtosis of the numbers (how peaked or flat their distribution is).
- LARGEReturns the k-th largest number in a list.
- LOGESTFits an exponential curve y = b * m^x to the data and returns its coefficients and optional statistics.
- LOGNORM.DISTReturns the lognormal distribution of x, where ln(x) is normally distributed.
- LOGNORM.INVReturns the inverse of the cumulative lognormal distribution.
- MAXReturns the largest number.
- MAXAReturns the largest value, counting text as 0 and TRUE as 1.