Returns the BoxCox transformation of the input data point(s).
Syntax
BOXCOX(X, Lo, Hi, $\lambda$, Return)
 X
 is the real value(s) for which we compute the transformation of a single value or a onedimensional array of cells (e.g., rows or columns)).
 Lo
 is the xdomain lower limit. If missing, Lo is assumed to be 0.
 Hi
 is the xdomain upper limit. If missing, Hi is assumed to be infinity.
 $\lambda$
 is the input power parameter of the transformation ($\lambda\in[0,1)$). If omitted, the default value of 0 is assumed.
 Return
 is a number that determines the type of return value: 1 (or missing) = BoxCox, 2 = Inverse BoxCox, 3 = LLF of BoxCox.
Return Description 0 or omitted BoxCox transform 1 The inverse of BoxCox transform 2 Loglikelihood function of the transform
Remarks
 BOXCOX() transform function converts a onebound domain (e.g., $x\in(a,\infty)$, $x\in(\infty, b)$) into an unbounded $(\infty,\infty)$ domain.
 If both values of Lo and Hi arguments are given, the BOXCOX() returns #VALUE!.
 BoxCox transform is perceived as a useful data (pre)processing technique used to stabilize variance and make the data more normally distributed.
 The BoxCox transformation is defined as follows:
$$ T\left ( x_{t}; \lambda, \alpha \right ) = \begin{cases} \dfrac{\left ( x_{t} + \alpha \right )^{\lambda}1}{\lambda} & \text{ if } \lambda \neq 0 \\ \log \left ( x_t + \alpha \right ) & \text{ if } \lambda= 0 \end{cases} $$ Where:
 $x_{t}$ is the input value of the input time series at time $t$.
 $\lambda$ is the input scalar value of the BoxCox transformation.
 $\alpha$ is the shift parameter.
 $\left(x_t +\alpha \right) \gt 0$ for all $t$ values.
 Using the negative values of \{x_t\}, we can use the BoxCox transform for a domain with an upper bound.
$$F(x_t;\lambda,b)=\begin{matrix}\frac{(bx)^\lambda1}{\lambda}&\lambda\neq0\\\ln{(bx_t)}&\lambda=0\\\end{matrix}$$  To calculate the inverse of the BoxCox transform:
 Domain with lower bound (a):
$$x=a+e^\frac{\ln{(\lambda y+1)}}{\lambda}$$  Domain with upper bound (b):
$$x=be^\frac{\ln{(\lambda y+1)}}{\lambda}$$
 Domain with lower bound (a):
 To compute the loglikelihood function (LLF), the BoxCox function assumes a Gaussian distribution in which parameters ($\mu,\sigma^2$) are calculated using the maximumlikelihood estimate (MLE) method.
$$LLF_{\textit{BoxCox}} = \frac{N}{2}\times( \ln( 2\pi\hat{\sigma}^2)+1)$$ $$\hat{\sigma}^2=\frac{\sum_{t=1}^N{(y_t\mu)^2}}{N}$$ Where: $\hat{\sigma}^2$ is the biased estimate of the variance.
 $N$ is the number of nonmissing values in the sample data.
 $y_t$ is the tth transformed observation.
Examples
Example 1:


Formula  Description (Result) 

=BOXCOX(\$B\$2:\$B\$30,3,0.5,3)  LLF BoxCox (33.35) 
Files Examples
Related Links
References
 Box, George E. P.; Cox, D. R. (1964). "An analysis of transformations". Journal of the Royal Statistical Society, Series B. 26 (2): 211–252. JSTOR 2984418.
 Sakia, R. M. (1992), "The Box–Cox transformation technique: a review", The Statistician, 41 (2): 169–178.
Comments
Article is closed for comments.