Numerical Analysis. Cengage Learning. essay
Introduction
Automation is currently used in virtually all business spheres and area of human activity. Automation solutions differ from large and complex software solutions tailored to match specific projects to small automation tools which can be customized to match individual computing needs. Among data management programs, Microsoft Excel occupies a separate place because it has become a de-facto standard for storage and sharing spreadsheet data.
Task
The task in this report is to write a program for solving linear system of equations characterized by 10×10 matrix using Gauss-Seidel numeric algorithm. The solution should be built using Visual Basic for Applications (VBA) which is embedded in Microsoft Excel software. The equation can be written in vector form as . The program should read the matrix characterizing the equation, perform iteration of Gauss-Seidel method, put the responses back on the spreadsheet and continue the iterations until the desired rate of precision is reached. The program will also determine the number of iterations needed to converge within 0.1, 0.001 and 0.00001 levels of precision.
MS Excel automation
Along with core spreadsheet functions, Microsoft Excel has powerful means of programming using Visual Basic for Applications (VBA) language. Embedded IDE for VBA programming makes Excel the best choice for on-the-go automation because of its convenience and easiness of use. Excel macros can be embedded in Excel books or in standard templates, and run on demand or used as functions on the spreadsheets. The features of VBA in MS Excel described above make it perfect tool for automating numeric algorithms. The purpose of this report is to perform an automation task for solving systems of linear equations. Detailed task, mathematical model and algorithm of the solution, results and discussion of further improvements of such automation are discussed in the appropriate sections below.
Mathematical model
Gauss-Seidel method is an iterative algorithm used in numerical linear algebra for solving linear systems of equations (Burden & Faires, 2010). This method can in general be applied to all matrices which have non-zero diagonal elements (Burden & Faires, 2010). To solve a linear system of equations using Gauss-Seidel method, it is necessary to make a starting assumption (“guess” solution), to insert the results back into the system, and repeat the iterative formula until each variable converges to the required rate of precision. The speed of convergence depends on the convergence factor (described below in the convergence section). Although this task can be solved manually in MS Excel, this would take a long time, and many repetitive actions. Therefore, it would be reasonable to automate this task using VBA.
Algorithm
The algorithm uses the input matrix of the system of linear equations, and yields the vector x as output; the idea is to put the resulting numbers back to the matrix, repeat the calculations and trace convergence in order to determine the final iteration for the algorithm. On the Excel spreadsheet, the original form of the equation is Equation A. For implementing Gauss-Seidel algorithm, the matrix of the system should be transformed in a different form (Equation B).
Criteria of convergence
In the general version of the algorithm, the rate of precision can be set as any numeric criteria. Convergence is determined by the absolute difference between xi and xi+1.The task contains three different convergence criteria; these are precision limits for variables 0.1, 0.001 and 0.00001. These criteria of convergence will be suggested as three different options in the target Excel automation solution. Convergence will be witnessed when the difference between previous and current solution vectors will be less than the established rate of precision. Overall, Gauss-Seidel method converges when the matrix is either positive definite and symmetric, or corresponds to the criteria of diagonal dominance (Burden & Faires, 2010).
Results
VBA macro implementing solution of a 10×10 linear system of equations was written. The macro works as instructed for matrices with non-zero diagonal elements. In the process of writing the macro, I received invaluable help of my professor, who clarified the specifics of Gauss-Seidel method and helped me with the implementation of difficult cycles. The macro worked as suggested for three different levels of precision: 0.1, 0.001 and 0.00001. With the help of this macro it is now possible to solve the systems of linear equations quickly and neatly, instead of performing numerous manual operations in MS Excel. The tests with different number were uses to test the macro. The macro converged in the majority of situations; the questions of convergence and further possible improvements of the macro are described in the next section.
Discussion
The macro perfectly works for the majority of the systems of linear equations; however, there was a situation when the solution did not converge; it happened when I switched the first and seventh line in the matrix. The program hung up and finally returned a stack overflow error. In this case, the solution did not converge as the next iterations resulted in vectors of solutions which derived further and further apart from each other. Therefore, the program could be further improved by adding the control of convergence before implementing the solution, and warning the user about non-convergent matrices instead of receiving stack overflow errors. Such improvement would make the program more convenient and user-friendly.
