- Learn how to perform array and matrix operations in Excel
- Learn how to write formulas that span multiple worksheets.

Conceptually, there is little difference between a rectangular array and a matrix. A matrix is basically an array for which some mathematical operations are defined. Excel provides array formulas which operate on rectangular arrays. Adding or subtracting two arrays (of the same size) is done exactly like adding two matrices.

In Excel you can also multiply or divide two arrays by multiplying or dividing corresponding elements. In the example of array multiplication shown below, each value in the product array is determined by multiplying the corresponding values in the arrays being multiplied together. I am using parentheses instead of square brackets to indicate that these are arrays; not matrices.

Note that array multiplication is distinctly different from matrix multiplication:

Excel provides special functions that perform those matrix operations that have no counterpart in array arithmetic. An example is the Excel function to perform matrix multiplication.

1. Download and open the file, Lab05.xlsx. Remind me how. Put your name in cell B1 of the June sales worksheet. The data you will be working with represents the gross sales of salespeople working for a new car dealer. The sales are recorded for two car categories: compact and luxury.

In the next step you will learn how to multiply a matrix by a constant. Our goal is to calculate the commission earned by each of the salespersons in June for each type of car. We'll assume that the car dealer pays a commission of 5% on gross sales for both car types.

2. Use the following instructions to enter an array formula to find the commissions.

a. Select the block of cells in which the commissions are to be displayed (E5:F7).

b. Type an equal sign to begin the entry of the Excel formula.

c. Select the block of cells which contain the gross sales amounts (B5:C7).

d. Now type in "* 5%" to indicate that you want to multiply by 5%
(**but do not tap the Enter key yet**).

e. In Excel, the formula we are entering is called an array formula because
it applies the same operation to a rectangular array of cells. When entering an
array formula, you terminate the entry by typing Shift** +
**Ctrl **+
**Enter**
**when you are done. If you just tap the Enter key, it
won't work. Go ahead, and type Shift + Ctrl + Enter.

3. Format the commission values using the comma format with zero decimal places.

1. Activate the July sales worksheet and enter in the following sales data:

2. Use an array formula to calculate the July commissions.

3. Make sure that both the sales and the commissions are formatted using the comma format with zero decimal places.

In August, the car dealer increased the commission rate from 5% to 5.25%. On the August sales worksheet, enter the August sales and use an array formula to calculate the August commissions. Both should be formatted using the comma format with zero decimal places.

On the summer sales worksheet, we want the total sales and total commissions for all three summer months.

1. Activate the summer sales worksheet.

2. Highlight the block of cells that will contain the total sales.

3. Type an equal sign to begin the entry of the Excel formula.

4. Activate the June sales worksheet, select the block of cells containing the June sales figures and type the plus sign (the addition operator).

5. Activate the July sales worksheet, select the block of cells containing the July sales figures and type the plus sign (the addition operator).

4. Activate the August sales worksheet, select the block of cells containing the August sales figures and type Shift + Ctrl + Enter to terminate the array formula entry.

5. Format the sales figures using the comma format with zero decimal places.

6. In a similar fashion, add the June, July, and August commissions to find the total commissions for the summer months.

A store chain that sells personal computers sells five different models in three different locations within a large city. The inventory at each of the stores and the wholesale and retail prices of each computer model are given on the computer inventory worksheet.

1. Activate the computer inventory worksheet.

The computer inventory array can be thought of as a 3 x 5 matrix (let's call it matrix N) and the array of wholesale and retail prices can be thought of as a 5 x 2 matrix (matrix P). The matrix product NP is defined because the number of columns in N is the same as the number of rows in P. The question is whether the values in the resulting product matrix (matrix V) have any real meaning.

Consider the value in the first column of the first row of the product matrix V:

V_{11}= N_{11}*P_{11}+ N_{12}*P_{21}+ N_{13}*P_{31}* N_{14}*P_{41}+ N_{15}*P_{51 }V_{11}= Number of Model A * Wholesale Price of Model A + Number of Model B * Wholesale Price of Model B + Number of Model C * Wholesale Price of Model C + Number of Model D * Wholesale Price of Model D + Number of Model E * Wholesale Price of Model E

Does this sum mean anything? Think about this before continuing.

The value in the first column of the first row of the product matrix V represents the wholesale value of all of the computers in Store 1. If you think about it some more, you should realize that the value in the second column of the first row represents the retail value of all of the computers in Store 1 and so on.

In the next few steps, you will write an Excel formula that will calculate the matrix product of the inventory numbers and the wholesale and retail prices and place the product matrix in the value of the inventory section of the worksheet.

2. Select the block of cells in the value of inventory section of the worksheet (C15:D17).

3. Start the Excel formula by typing:

=MMULT(

Excel's MMULT(array1, array2) function calculates the matrix product of its array arguments. In our case, the first array is the block of cells that contain the inventory numbers and the second array is the block of cells that contain the wholesale and retail prices.

4. To complete the formula, highlight the cells containing the inventory numbers, type a comma, highlight the cells containing the prices, type a closing parenthesis, and type Shift + Ctrl + Enter. You should get the following results:

5. Format these inventory values using the comma format with zero decimal places.

1. Activate the cryptography worksheet.

This worksheet will be used to code a message and also to decode it. In practice, of course, the message would be encoded by the sender and decoded by the receiver. This activity is very similar to the cryptography examples in the book but with two significant differences. In this exercise, the messages go across the rows of a matrix rather than down the columns as they did in the text book. It is an arbitrary decision and I chose to let the messages go across the rows simply because that makes them easier to read.

The second difference is the coding scheme. In this exercise, the number associated with each letter is its ASCII code. ASCII is short for American Standard Code for Information Interchange which is a code system used to represent text characters on computers. This system is much more flexible than the simple code in the text book and allows our messages to include any combination of uppercase and lowercase letters, digits, and punctuation.

The message that we will be working with is "Meet me at ten". I created a matrix to store this message as text. It is important to recognize that the blank cells in this matrix are not really blank or, put another way, the blank cells are not empty. The blank cells contain the space character whose ASCII code is 32.

Below that, I created another matrix with each letter and space replaced by its ASCII code.

Finally, I provided an encoding matrix that will be used to encode our secret message:

2. The encoded message is created by matrix multiplication; the encoding matrix times the message matrix (the one with the numeric letter values). Use Excel's MMULT function to perform this operation. The result should be placed in the encoded message matrix:

To decode our secret message, we have to find the inverse of the encoding matrix (which gives us the decoding matrix). The message is decoded by performing another matrix multiplication; the decoding matrix times the encoded message.

3. The Excel function MINVERSE(*array*) is an array function that
returns the inverse of its array argument. MINVERSE is short for MATRIX INVERSE.
Highlight the cells of the decoding matrix and use the MINVERSE function to
calculate
the inverse of the encoding matrix.

4. Using matrix multiplication, multiply the decoding matrix and the encoded message matrix. Place the results in the decoded message matrix (as alphabetic codes). The resulting text message should appear in the final decoded message matrix.

5. In cell D3 alter the message to read "Boris is a traitor". Enter this
message in the text message matrix. **Make sure you enter the space character
in cells in which a space should appear.** Do not leave the cell empty. That is
not the same as putting a space in it.

You should notice that the encoded message has changed, but the decoding process has generated the correct text message at the receiver's end.

6. Change the encoding matrix to:

The encoded message will be different. The decoding matrix will be different. But, the text message at the receiver's end will still be correct.

Now, suppose somebody intercepted the encoded message but did not know the encoding matrix and thus was unable to calculate the decoding matrix.

7. Delete the contents of the decoding matrix by highlighting the entire matrix and tapping the Delete key.

8. Type in a random value in each of the cells in the decoding matrix. Notice that the decoded message codes do not match the encoded message codes and that the decoded text is gibberish.

1. Activate the lab exercise worksheet and put your name in cell B1.

2. In the upper-left corner of the worksheet is a teacher's grade book showing the scores for each student on each of four tests. In the average column, enter a single array formula that will calculate the average score for each student. Each column of test scores is a column array. To find the column array of average scores, you add the four column arrays containing the test scores and divide the result by four.

3. To the right of the grade book is a secret message and the encoding matrix used to encode the message. In the matrix labeled "Decoded Message as Alpha Codes", enter an array formula that will generate the correct codes. If your formula is correct you should be able to read the secret message in the matrix labeled "Decoded Message as Text". Here is a hint. The matrix multiply function requires two matrix arguments. The first is the matrix inverse of the encoding matrix and the second is the encoded message matrix: MMULT(MINVERSE(encoding matrix), encoded message matrix).