Embedded Files
<-- Return to Excel Statistics Page
Go to Setting up the Spreadsheet -- The Input Section
Go to Setting up the Spreadsheet -- The Output Section
Go to Planetary Final Results Section
Setting Up the Excel Spreadsheet -- The Output Section
Rows 24 through 66 show the values of the ellipse for 360 degrees of a circle in nine-degree steps that are shown in column C range C26:C66. The number of rows could be added to make it 360 if you want to plot a point for each degree of a circle, but mostly, that’s not necessary. What may be necessary is to duplicate the 0 degrees row at the bottom of the list. That way Excel will fill in the final gap.
Range D24:E66 includes the original ellipse based on the input items shown above.
Range F24:G66 includes the same points shifted to the right by the amount of c. Look carefully at the Excel code to see how this is done.
Range H24:I66 includes the points from F24:G44 multiplied by the rotation matrix in C21:D22. If the degrees of rotation are set to zero, no rotation takes place. That’s what is going on in the example, below. Be sure to look at how the =MMULT(Range1, Range2) function is implemented. You can find more about this at this link or the following steps:
Steps to Matrix Multiplication – Array Function
Matrix multiplication in Excel is accomplished via =MMULT(Range1, Range2), which is known as an array function. Here are some steps that can be used to enter the Excel matrix multiplication function =MMULT(Range1, Range2). [Note to Microsoft: Array functions should be constructed so that these steps are not necessary. A redesign of array functions should be easy to accomplish. I know because I have done it in VBA. I hope you will consider making the change in a future version of Excel.]
The =MMULT(Range1, Range2) function may be used to multiply two ranges. In the example being constructed here, Range1 is F26:G66 and Range2 is C21:D22. The output range is H26:G66. Do not use the header cells in any of these range specifications. In general, Range1 can be a 2 x n list of values as in…
... continue to the bottom of the range
Range 2, in this application is a 2 x 2 matrix of values that contain cosines of an angle in cells 1,1 and 2,2. A sine value of the same angle is in cell 2,1, and a -1* sine of the same angle is in the cell 1,2. All angles must be converted to units of radians. In the example, the top right value may also be written as = -1*SIN(D6) or =-sin(D6), where D6 is the cell containing the angle in radians. Look closely, and see the minus sign in front of "sin" , in the example code.
The following example shows the code in a 2 x 2 matrix used for rotation. Again, all cell references go to cells that contain degrees that are in units of radians.
Range 2
Rotation Matrix
=COS(D6)
=SIN(D6)
=-SIN(D6)
=COS(D6)
Now, identify a range for the output. This range should have the same shape as the Range1 shown above.
Go to the output range, and in the top left cell, type =MMULT(Range1, Range2) and hit enter. For Range1, specify the cell range you have identified above as Range1. For Range 2, enter the cell range for Range2 rotation matrix.
Now, highlight the output range. That is, use your mouse or finger motions to select the cells that will contain the output. Make no mistakes. Make sure you highlight a range that has equal shape as Range1.
While keeping the output range highlighted, click on the edit bar at the top of the spreadsheet. The edit bar is the one that shows the Excel Code. This is not the edit tool in the top right cell where you entered the code. Instead, this is the edit bar at the top of the sheet. If your spreadsheet is not displaying the edit bar, then check Excel help for instructions on how to make it visible.
With the Excel code selected in the edit bar, hold down these keys Shift+Control+Enter.
The range you selected for output should become filled with results of Range1 * Range2.
Remember, that in matrix multiplication, Range2 is not the same as Range1. The process is not commutative.
If you build a scatter plot (also called a scatter graph or scatter gram) graph from Range H25:I66, using Column H as the x values and Column I as the y values, you should see an ellipse that has the left focal point at point (0,0). The steps I followed are these.
Highlight the range H25:I66. Be sure to include the headers.
Select: Insert--> Chart--> Scatter--> Smooth Line Scatter. At least those are the steps on my version of Excel on September 7, 2017. The actual steps may vary, based on the version of Excel that you are using.
Following is an example what you should see of the H25:I66 range with the row and column axes both set to fixed values of -6 to +6. When you compare this to the planetary ellipses, keep in mind that the "a" and "b" values are very different. Here, they have been set to a = 3 and b = 2 specifically to demonstrate the shifting that takes place if the spreadsheet has been set up correctly.
Don’t be too concerned if the ellipse looks like a circle. The automatic formatting of Excel graphs may cause that. You can improve the appearance of the Excel chart by setting the maximum and minimum values in the x and y axes to fixed values. Also, you may wish to export the range H26:I66 as a comma delimited file, without the headers, and import it into Apple Grapher.app for a better looking graph. An example of am Apple Grapher.app three-dimensional graph may be found in this essay at this link.
Range J24:J66 contains z axis values that were created by solving a three-part Pythagorean equation for z. Check the Excel code to see how this was done. Now, export the full range H26:H66 to a .csv file and the ellipse can be graphed in Apple Graph.app.
Below is the Excel code for Excel Image 2a. Be sure to carefully follow the text wrapping to understand the calculations that were used in Excel Image 2a. As you look at Excel Image 2b, see that it is presented in .jpeg files. This is because the web system did not want to adequately display columns I and J unless it was as a .jpeg image.
Page updated
Google Sites
Report abuse