Solving System of Linear Equations in Microsoft EXCEL with Various Open Source Libraries

1 Reply

General purpose open source numerical analysis libraries for solving system of linear equations are used to interface with more specified numerical analysis procedures such as Finite Element Analysis.
They are built as dynamic link libraries and iterfaced with VBA, so that they can be easily used as EXCEL’s worksheet functions.

Open Source Libraries Used and their dlls

EIGEN
eigen.dll
ALGLIB
alglib.dll
LAPACK with OpenBLAS
lapack_and_openblas.dll
Reference algorithm implementations
targa.dll
triangular_solver.dll
Additional libraries related to MSYS2
libwinpthread-1.dll
libgcc_s_seh-1.dll
libgfortran-5.dll
libquadmath-0.dll
lapack and openblas dlls are built under MSYS2 and depend on these additional libraries

Installation

Copy library files to your specific folder (e.g. C:\NumericalAnalysisLibrary).
Add that folder to PATH environment variable of Windows.

Testing in Microsoft EXCEL

Open NumericalAnalysisLibrary.xlam
Optionally, if this file is registered as an EXCEL ADD-IN, it is not necessary to open this file every time you use this library.
Open test_NumericalAnalysisLibrary.xlsm
In test_NumericalAnalysisLibrary.xlsm, input equation (variable) numbers in D4 cell and right hand side vector numbers in D5 cell and select solver to test in D6 cell. Available solvers are listed in range M5:M39

Sample Test Result

If equations and rhs columns are set to 5 and 3 and EIGEN’s PartialPivLU_decompose is selected as an solver, then the intermediate process and results will be output as follows. At the end of the result worksheet, residual value of solution is also output as an verification of the solver. You can also refer to named ranges defined in output worksheet.