NxRegress - Simple Linear Regression Function

calculates the value of the regression function for an intermediate x-value.

 

Syntax

NxRegress(X, Y, Regression_type, POrder, Const, target, Return_type, Alpha)

X is the x-component of the input data table (a one dimensional array of cells (e.g. rows or columns)).

Y is the y-component (i.e. function) of the input data table (a one dimensional array of cells (e.g. rows or columns)).

Regression_type is the model description flag for the trend function (1 = Linear (default), 2 = Polynomial, 3 = Exponential, 4 = Logarithmic, 5 = Power).

Order Description
1 Linear (default)
2 Polynomial
3 Exponential
4 Logarithmic
5 Power

POrder is the polynomial order. This is only relevant for a polynomial type of trend and is ignored for all others. If missing, POrder = 1.

Const is the constant or the intercept value to fix (e.g. zero). If missing, an intercept will not be fixed and is computed normally.

target is the desired x-value to calculate regression value for (a single value).

Return_type is a switch to select the return output (1 = Forecast value (default), 2 = Upper limit, 3 = Lower Limit, 4 = R-Squared).

Method Description
1 Forecast value (default)
2 C.I. upper limit
3 C.I. lower limit
4 R-Squared

Alpha is the statistical significance or confidence level (i.e. alpha). If missing or omitted, an alpha value of 5% is assumed.

 

Remarks

  1. NxRegress supports the following trend functions:
    $$ \begin{cases} \mathrm{Linear} & Y=\alpha + \beta \times X \\ \mathrm{Polynomial} & Y=\alpha + \beta_1 \times X + \beta_2 \times X^2 + \cdots + \beta_N \times X^N \\ \mathrm{Exponential:} & Y= \alpha \times e^{\beta \times X} \\ \mathrm{Logarithm:} & Y= \alpha + \beta \times \ln(X) \\ \mathrm{Power:} & Y= \alpha \times X^{\beta} \\ \end{cases} $$
  2. For exponential and logarithmic trends, the intercept value is not permitted to be fixed, and thus is ignored.
  3. The polynomial order argument must be a positive integer.
  4. The trend function's coefficients that best fit your data are estimated using the "least squares" method.
  5. The time series may include missing values (e.g. #N/A) at either end.

Examples

Example 1:

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
A B
Data Data
3.967 14.241
2.055 9.775
3.633 13.741
2.624 10.348
4.203 17.102
3.119 13.292
4.994 17.729
3.262 12.138
4.985 18.810
2.116 8.195
2.695 11.446
2.937 11.329
4.082 14.998
3.104 11.807
2.947 12.963
4.347 17.000
2.894 10.687
4.907 17.193
4.723 18.091
4.750 18.894


  Formula Description (Result)
  =NxRegress($A$1:$A$20,$B$1:$B$20,1,,,2,1) Linear NxRegress (8.633)
  =NxRegress($A$1:$A$20,$B$1:$B$20,2,,,3,1) Polynomial NxRegress (11.945)
  =NxRegress($A$1:$A$20,$B$1:$B$20,3,,,2,1) Exponential NxRegress (9.185)
  =NxRegress($A$1:$A$20,$B$1:$B$20,4,,,3,1) Logarithmic NxRegress (12.275)
  =NxRegress($A$1:$A$20,$B$1:$B$20,5,,,2,1) Power NxRegress (8.515)

Files Examples

References

Have more questions? Submit a request

0 Comments