In spite of the natural similarities between Microsoft Excel spreadsheet arrays and the matrix and vector elements of linear algebra, Microsoft Excel provides very little direct support of linear algebra functions and operations. The few Excel functions that do support linear algebra functions and operations are:
| Array addition, subtraction, scalar multiplication | +, -, * |
| Inner product | SUMPRODUCT |
| Array transpose | TRANSPOSE |
| Matrix multiplication | MMULT |
| Matrix determinant | MDETERM |
| Matrix inverse | MINVERSE |
Even this support is suspect. For example, it is not recommended to solve square systems of linear equations using matrix inversion and multiplication. It is more efficient and accurate to factor and solve the system using triangularization with column pivoting. Nor is it recommended to solve linear least-square (regression) problems by solving corresponding normal equations. Instead, an orthogonal factorization method or singular value decomposition should be used for greater numerical stability.
LAPACK
LAPACK [1,2] is a freely-available, peer-reviewed computational linear algebra software library that provides routines for solving systems of simultaneous linear equations, computing least-squares solutions of linear systems of equations, and computing eigenvalue and singular value decompositions. The associated matrix factorizations (LU, Cholesky, LQ/QR, SVD, Schur, generalized Schur) are also provided, as are related computations such as estimating condition numbers.
LAPACK has been used in or as a starting point for implementation of linear algebra computing environments and is a standard by which other libraries and computing environments are often compared.
Ponderosa Computing Linear Algebra Excel Add-ins
The purpose of the Ponderosa Computing Linear Algebra Excel Add-ins are to provide Excel linear algebra computations augmenting or replacing those provided as built-in Excel functions. These Add-ins:
- Provide peer-reviewed and publicly documented LAPACK algorithms to Excel users as scalar and array worksheet functions.
- Implement these scalar and array worksheet functions using the public-domain CLAPACK library from the Netlib Repository [3,4].
- Track latest changes made in the LAPACK distribution addressing the latest bug reports and feature requests.
The Ponderosa Computing Linear Algebra Excel Add-ins support Microsoft Windows 7, 8, and 10. There are two Add-ins:
- PcLinAlgXLL_32.xll supports 32-bit Excel 2007 and later.
- PcLinAlgXLL_64.xll supports 64-bit Excel 2010 and later.
Each Add-in has a separate installer package available for download from the software downloads page.
Provided Worksheet Functions
Scalar-valued matrix functions (metrics):
| Matrix Metric | PcLinAlgXLL Function |
|---|---|
| 1-norm (column norm) | LA.NORM1 |
| 2-norm (spectral norm) | LA.NORM2 |
| Infinity-norm (row norm) | LA.NORMINF |
| Frobenius norm | LA.NORMF |
| Rank | LA.RANK |
| Spectral radius of a square, symmetric matrix | LA.SRAD |
| Trace of square matrix | LA.TRACE |
| 1-norm inverse condition number estimate of square matrix | LA.ICNBR1 |
| Infinity-norm inverse condition number estimate of square matrix | LA.ICNBRINF |
| Determinant of square matrix | LA.DET |
Linear system solvers:
| Linear System | PcLinAlgXLL Array Function(s) |
|---|---|
| Square (full-rank) system solver | LA.SYS.SLV, LA.SYS.SLVE |
| General least-squares system solver (via LQ/QR) | LA.SYS.LS, LA.SYS.LSE |
| General least-squares system solver (via SVD) | LA.SYS.LSS, LA.SYS.LSSE |
Singular value decomposition:
| Decomposition | PcLinAlgXLL Array Function (s) |
|---|---|
| Singular values | LA.SNGVL.C, LA.SNGVL.R |
| Singular values with error bound | LA.SNGVLE.C, LA.SNGVLE.R |
| Left or right singular vectors | LA.LSNGVC, LA.RSNGVC |
| Full singular value decomposition: A = U * Σ * V’ | LA.SVD |
Eigenvalues and eigenvectors:
| Decomposition | PcLinAlgXLL Array Function (s) |
|---|---|
| Eigenvalues | LA.EGNVL.C, LA.EGNVL.R |
| Eigenvalues with error bound | LA.EGNVLE.C, LA.EGNVLE.R |
| Eigenvalues and right eigenvectors | LA.REGNVC |
Cholesky factorization:
| Factorization | PcLinAlgXLL Array Function (s) |
|---|---|
| Lower triangular Cholesky factorization: A = L * L’ | LA.CHOL.L |
| Upper triangular Cholesky factorization: A = U’ * U | LA.CHOL.U |
Matrix creation and extraction:
| Matrix Operation | PcLinAlgXLL Array Function(s) |
|---|---|
| Create constant matrix | LA.GEN.CON, LA.RGN.CON |
| Create random matrix | LA.GEN.RAN, LA.RGN.RAN |
| Create identity matrix | LA.GEN.IDN |
| Create diagonal matrix | LA.GEN.DIAG |
| Extract diagonal elements | LA.XDIAG.C, LA.XDIAG.R |
Alternative matrix multiplication and transformation operations:
| Matrix Operation | PcLinAlgXLL Array Function(s) |
|---|---|
| Matrix multiplication: A *B | LA.MUL |
| Matrix transpose multiplication: A’ * B or A * B’ | LA.TMUL, LA.MULT |
| Matrix transpose: A’ | LA.TRN |
Software Release and Download
I have released version 1.3.0 of the Ponderosa Computing Linear Algebra Excel Add-ins. The Windows installation packages and further documentation are available for download on the software downloads page.
References
[1] Anderson, E. et al., LAPACK Users’ Guide, Third Edition (Society for Industrial and Applied Mathematics, Philadelphia, PA, 1999) ISBN 0-89871-447-8.
[2] LAPACK on the Netlib Repository at UTK and ORNL. http://www.netlib.org/lapack/
[3] CLAPACK on the Netlib Repository at UTK and ORNL. http://www.netlib.org/clapack/
[4] CLAPACK for Windows. http://icl.cs.utk.edu/lapack-for-windows/clapack/