Using Excel to reduce a Square Matrix

Abstract

When solving operations research problems, one can use either specialised computer programs such as Lingo, Lindo, Storm or more universal programs such Excel, Matlab, and R. To obtain the input data, one can use either a program’s own editor or other programs commonly available such as Excel. While the problem-solving methods, being part of various programs, are the subjects of numerous publications (such as Gros, 2003; Jablonský, 2002; Plevný – Žižka, 2007; Stevenson – Ozgur, 2009), the way the input data are obtained, recorded, and processed receives far less attention although this part of problem-solving requires considerable effort and, if the method for data recording is inadequate, may cause subsequent difficulties in their further processing. A problem known as “the travelling salesman problem” (TSP) may serve as an example. Here, the input data form a “square matrix of distances”. This paper is concerned with some Excel tools that can be used to obtain and subsequently modify such a square matrix. Given a square m × m matrix, an ordinary user might want to reduce it to an i × i square matrix (where i < m) without having to copy data from the matrix, skip some of its rows and/or columns or write a program to implement such a reduction.In her degree project, Kourková, 2009 was looking for an efficient method of reducing an Excel matrix. She had found no relevant papers on this subject concluding that the authors of the commercial program had not considered this. Therefore, she offered her own solution unconventionally using the contingency table menu option. Although this had resulted in the desired submatrix, some of its parts were superfluous and even baffling for the user.For this reason, the authors analyse the method of representing an m × m matrix and the way of its reduction. Finally, a better option is offered to achieve the desired objective as well as other methods of obtaining the required submatrix that even users without sufficient programming skills can use.