Quick Matrix Setup in Excel

Excel provides a really quick method of populating an entire block where the content needs to be calculated based on surrounding fields.

Let's look at an example of this to make it clearer what I am talking about.

Suppose that I wish to allocate set percentages of given dollar figures into specific columns across our matrix. For the purpose of this example let's say we want 15% in the first column, 40% in the second, 22% in the third and 23% in the fourth (which totals to 100%). Next let's assume that we want these percentages of $12.00 in the first row, of $15.00 in the second row, and $7.00 in the third. Your figures might be anything they don't have to be dollars and percentages, that's just what we are using for the example. In this instance we have entered the percentages in row 1 and the dollar values in column A. The matrix in our Excel spreadsheet looks like this once we type in the first formula shown in bold.


The formula tells Excel to multiply the contents of cell B1 by the content of cell A2 and display the result in this position. The dollar signs in the formula are what we use to make copying the correctly modified formulae into the other fields in our matrix. B$1 tells the copy function not to change the B when copying this formula to another cell. The A$2 means don't change the 2.

To copy this formula across the row you place the cursor at the bottom right of the cell and drag (with the mouse button depressed) to select the cells to the right that you want to copy to. Once the entire top row is filled in you then drag again only downwards this time to fill in the rest of the matrix.

The complete matrix will now be filled in with the correct values in each cell. Had we not used the $ symbols in the formula we would have needed to type in each entry in the matrix separately as without fixing the column and row that is used for the calculation the cells would have been calculated from the values in cells having the same relative positions rather than the absolute prositions that we required.

With the formula successfully copied our matrix content now looks like this:


All that and we only typed in our starting values and one formula. This can be readily extended to matrices of any size where the contents of each field depend on the values surrounding the matrix. Provided that you specify the dollar signs in your formula in the right places then copying the formula to other cells will automatically correct the formula to what is required for each individual cell.

of course if your matrix is not in the top left corner of the spreadsheet you will have to substitute its actual position when typing in the formula. The matrix once populated cannot be moved to a different location within the spreadsheet because the formula refers to cells in fixed rather than relative positions.

One final point. If you need all of the entries in the array to reference a specific field value in the formulae then use $ in both locations. For example if all of the fields in our above matrix are to be multiplied by the value in cell A1 then the formula to use would become =B$1*$A2*$A$1.

go to top

FaceBook Follow
Twitter Follow