Ponderosa Computing

Reliability - Clarity - Accuracy

  • Excel XLL Add-ins
    • Statistical Distributions
    • Linear Algebra
  • .NET Class Libraries
    • Linear Algebra
  • Software Downloads
  • Announcements

Ponderosa Computing Linear Algebra Excel Add-in

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 productSUMPRODUCT
Array transposeTRANSPOSE
Matrix multiplicationMMULT
Matrix determinantMDETERM
Matrix inverseMINVERSE

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-in (PcLinAlgXLL)

The purpose of the Ponderosa Computing Linear Algebra Excel add-in is to provide LAPACK linear algebra computations as worksheet functions augmenting or replacing those provided as built-in Excel functions. This add-in:

  1. Provides peer-reviewed and publicly documented LAPACK algorithms to Excel users as scalar and array worksheet functions supporting double-precision, real matrices.
  2. Implements these scalar and array worksheet functions using the public-domain CLAPACK library from the Netlib Repository [3,4].
  3. Supports Microsoft Windows platforms running Excel 2007 and later.
  4. Tracks latest changes made in the LAPACK distribution addressing the latest bug reports and feature requests.

Provided Worksheet Functions

Scalar-valued matrix functions (metrics):

Matrix MetricPcLinAlgXLL Function
1-norm (column norm)LA.NORM1
2-norm (spectral norm)LA.NORM2
Infinity-norm (row norm)LA.NORMINF
Frobenius normLA.NORMF
RankLA.RANK
Spectral radius of a square, symmetric matrixLA.SRAD
Trace of square matrixLA.TRACE
1-norm inverse condition number estimate of square matrixLA.ICNBR1
Infinity-norm inverse condition number estimate of square matrixLA.ICNBRINF
Determinant of square matrixLA.DET

Linear system solvers:

Linear SystemPcLinAlgXLL Array Function(s)
Square (full-rank) system solverLA.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:

DecompositionPcLinAlgXLL Array Function (s)
Singular valuesLA.SNGVL.C, LA.SNGVL.R
Singular values with error boundLA.SNGVLE.C, LA.SNGVLE.R
Left or right singular vectorsLA.LSNGVC, LA.RSNGVC
Full singular value decomposition: A = U * Σ * V’LA.SVD

Eigenvalues and eigenvectors:

DecompositionPcLinAlgXLL Array Function (s)
EigenvaluesLA.EGNVL.C, LA.EGNVL.R
Eigenvalues with error boundLA.EGNVLE.C, LA.EGNVLE.R
Eigenvalues and right eigenvectorsLA.REGNVC

Cholesky factorization:

FactorizationPcLinAlgXLL Array Function (s)
Lower triangular Cholesky factorization: A = L * L’LA.CHOL.L
Upper triangular Cholesky factorization: A = U’ * ULA.CHOL.U

Matrix creation and extraction:

Matrix OperationPcLinAlgXLL Array Function(s)
Create constant matrixLA.GEN.CON, LA.RGN.CON
Create random matrixLA.GEN.RAN, LA.RGN.RAN
Create identity matrixLA.GEN.IDN
Create diagonal matrixLA.GEN.DIAG
Extract diagonal elementsLA.XDIAG.C, LA.XDIAG.R

Alternative matrix multiplication and transformation operations:

Matrix OperationPcLinAlgXLL Array Function(s)
Matrix multiplication: A *BLA.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.2.0 of the Ponderosa Computing Linear Algebra Excel Add-in. The Windows installation package is available for download and evaluation 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/

Site content copyright © 2019 Paul J. McClellan. All rights reserved.

Log in