Embedded Files
<-- Return to Excel Statistics Page
Data Rotation in Excel via Matrix Multiplication
Keith Greiner
August, 2020
This essay is about matrix rotation in Excel. Matrix rotation is helpful when scattergrams are used to plot a collection of x and y points in a two dimensional presentation. Scattergrams and rotation can be used in survey results that plot one characteristic vs. another. The factor analyses described on this site, and the plots of ellipses are examples.
A rotated set of points may present a different, and useful, interpretation of the relationship between the set of x and y variables. It should not be used by an analyst to claim that the relationship is different from the original, when in fact it is not. Rather, it is helpful to see patterns that can emerge when the data are viewed from a different angle. I’ll show an example of this situation.
The two graphs shown below are an example of a single set of x, y data that were rotated. Both graphs include a trend line, the linear regression equation, and the R-squared value. The original data were created via a simulation process that is described in the steps listed below. The rotation was accomplished by multiplying the original x, y data by a 2x2 rotation matrix. Since the data are a random simulation, and have no meaning, I'll not discuss how the rotation might change interpretation of these data, and will leave that for a possible addition to the document later on. Larger images of the two graphs are presented at the end of this document.
The following steps were used to create a rotation simulation of a set of randomly distributed numbers that are centered on a regression line. Excel images 1,2,4, and 5 are excerpts from Excel tables that have 225 rows. I tried to include the entire selections of 225 rows, but Google Sites returned an error saying this document was too large to save. Therefore, the bottom portions were cut from the presentation. Still, there is enough presented that you can see the pattern. What you will want to do is get these segments set up, and then copy and paste down to complete the table segment. I will show you how the example is set up and you can take it from there.
Begin with Excel Image 1.
Set up the parameter data that are shown, and extend it down to cells C6:E225.
Cell D6 presents the increment of x that makes the example have an increasing linear relationship. This is the slope of the simulation's regression line.
Cell D9 contains the starting point for the data set.
In Column D, and cells D10:D225, each value is the previous (above) value plus the amount in D6.
Excel Image 2 shows the Excel Code used in Excel Image 1. Here, we see that in column E, we have a function
That builds on column D. The Code is =D9+NORM.INV((RAND()),0,0.5). The =NORM.INV(…) function, with =RAND(…) builds a set of numbers that are directly related to the values in column D, and yet are randomly distributed around the trend line. See the graphs for the trend line and the slope and intercept that result from this calculation.
Excel Image 3 shows the parameters that will be used to rotate the range D9:E225.
Cells I10 and I11 define the angle of rotation. I10 has the angle in degrees, and I11 uses the =RADIANS(…) function to convert the degrees to radians.
Cells H19:I20 contain sin(…) and cos(…) functions to form the matrix to be multiplied by range D10:D225 in the steps that follow.
Now, set up the range to receive the results. Put the headers in rows 7 and 8, and enter the “Record Number” values into column P.
Now, enter the following code into cell Q9. =MMULT(D9:E225,H15:I16)
If everything else has been set up according to the example and instructions, then you will be setting up to multiply array D9:E225 by the rotation matrix H15:I16. Cell Q9 is the top left cell of the results matrix (array).
With the Excel code entered into Q9, now highlight the entire results range of Q9:R225.
Keeping that range highlighted, go to the formula edit bar above the sheet, and click somewhere in the code.
Now do this. Click on Shift+Control+Enter simultaneously. On a Mac computer you can also, click on Shift+Command+Enter simultaneously.
You should see the entire results range fill with numbers and the Excel code you entered into cell Q9.
Now, create two scatter graphs of ranges D9:E225 and Q9:Q225 (separately), and watch for the interesting results. I added a linear trend line to give an additional indicator of slope.
Enter new values into the degrees cell, I10 and watch the graphs redraw. When you enter 90 degrees, you may notice that the trend lines are not fully 90 degrees apart. This is because of the auto-formatting used by Excel. If you resize one of the graphs, you can get the trend lines to appear to be 90 degrees apart.
The images are all presented below in sequence. Data table sections all have 225 rows, but are shown here up to 50 rows because the web-page system would not allow the entire sections to be shown. It said they were too large. Graphs are below the Excel images.
Excel Image 1
Excel Image 2
Excel Image 3
Excel Image 4.
Excel Image 5
Graph 1
Graph 2
Page updated
Google Sites
Report abuse