Polynomials
In previous projects you could not have escaped noticing that Excel is very good at keeping track of dependencies between cells and automatically updating values in one cell when the value of another has changed. We used this property, for example, in the Paycheck project so that the dates for an entire week could be specified based on Monday's date. Excel is also very nimble with charts, as demonstrated with the Function Tables, Utility Bill, and Linear Regression projects. In this project you will combine these two abilities and turn Excel into a graphing calculator which can display polynomials and even update the display in real time as the polynomial coefficients and even order changes.
Instructions
You will be able to watch a short demo in class and a PDF version of the spreadsheet is available for reference. Macros (or more technically VBA code) are involved. This you should type in yourself to get a better feel of what coding and the language are like. You may receive printouts of these files or similar ones.
Please note the corrections over the printed version that are shown here in bold print.
Start a new spreadsheet (technically a workbook) and save it right away in a format that supports macros so that you don't lose your macros later. In Excel 2010 the type is called Excel Macro-Enabled Workbook (*.xlsm). Excel 2003 does not require a special format. Call the file Polynomials so that your teacher can find your project under that name.
If order to make the graph (chart), you will need x and y values. Label A1 and B1 x and y, respectively. The x values will be determined by the parameters you enter next. The y values are determined by the polynomial.
Values for x are constrained by values for minX and maxX. Label cells D7 and I7 with these terms. Label N7 rows.
In cells E7, J7, and O7, enter values of -1, 1, and 21 as typical values. You can change them later, but these are the default values for now.
Give these cells names of minX, maxX, and rows so that the values can more easily be used in formulas. Either enter the names in the name box or right click and use Define Name...
Starting in cell A2 there needs to be a formula for x that produces minX in that cell and MaxX when the formula is copied 21 rows later. This formula is =minX+(maxX-minX)/(rows-1)*(ROW()-2). If you are in row 2, this results in a value of minX. If you are in row 22 (so that there are 22-2+1=21 rows total), x=minX+(maxX-minX)/(21-1)*(22-2)=minX+(maxX-minX)/20*20=minX+(maxX-minX)=maxX.
Stretch this formulas downward to A22 where the resulting value should be 1.
Now it's time to define the polynomial by specifying the exponents and coefficients of the terms. This will be done with the aid of form controls so that values can be changed with the click of the mouse. Starting at D1 enter vertically the labels power, coefficient, max, min, and include.
Find the Developer toolbar and Insert a Form Control called a Spin Button. After you click on the icon, you'll need to draw it on the spreadsheet. Aim for E1 across from power.
Right click on the control and select Format Control... Specify Current Value of 3, Minimum value of 0, Maximum value of 10, Incremental change of 1 and Cell link of F1. After you do this, the control should control the value in F1 and you can test it.
Skip the coefficient for now and add Spin Buttons for max and min.
Their formats should start out with of Current value of 100, Minimum value of 0, Maximum value of 200, Incremental change of 1, and Cells links of F3 and F4. Verify that they control the contents of those two cells.
The coefficient will range between the max and min values. A different control, the Scroll Bar offers more flexibility in specifying values. It includes the same arrows on each end for incrementing and decrementing a value, but also areas for paging, and a slider for more direct manipulation. Insert one of these in cell E2 across from coefficient.
Right click the control and format it to have a Current value of 5000, Minimum value of 0, Maximum value of 10000, Incremental change of 1, Page change of 100, and Cell link of F2. Give it a whirl.
Lastly try out the Check Box control. You can use this to control whether or not a term is included in the polynomial without having to erase the cells that control the value or zero it out by hard wiring the coefficient to zero. Insert it in E5 after include. You can delete (backspace over) the label that the Check Box is given by default.
Right click the control and format it with a Cell link of F5. Give it a try. All five of the cells at the top of column F should now be in control of Form Controls.
Unfortunately, the controls are not set up to produce negative values and you will want to see what happens for negative x. Another column, G, will convert the raw values in column F to values used in the equation. The top, in G1, can be set =F1, because the powers should begin at zero and increment by just one with each mouse click.
Skip coefficient for now. max specifies it maximum possible value. That maximum value may be negative. To make this happen, subtract 100 from the value in F. In G3 use =F3-100.
Similarly in G4 use =F4-100.
F5 is also good, so just use =F5 in G5.
Now calculate the coefficient in G2. It should range from min to max in fairly fine increments. The equation for this is very similar to that for the x values with minX and maxX, but here the cells aren't named. The formula is =G$4+(G$3-G$4)/10000*F$2. As you can see, it is assumed that the Scroll Bar has a maximum value of 10000.
You now have the complete description of one term in the polynomial. More terms can be used, but they are not necessary in order to get results. Although it might be possible to calculate the value of the polynomial with only formulas, you'll practice some VBA programming here. One function, called polynomial, will be used. Open Visual Basic via, for example, the button on the Developer tab. Right click somewhere on the VBAProject with your file name next to it and choose to Insert a Module. One, likely named Module1 should appear.
Double click on the module and enter the code in the handout or from the link above. There is a substantial amount to type and we will discuss some of it in class. It contains conditionals and Booleans, which you may remember from the most recent quiz.
If the polynomial function is entered correctly, it must be invoked with an x value and parameters for the coefficients and powers. This is accomplished with formulas in the y column starting in cell B2. There enter the formula =Polynomial(A2,$D$1:$H$5). (A blank column is included as a separator.) This will invoke the function with the value from A2 and all the parameters that are specified in the range D1:H5. Make sure that it does by configuring a power and coefficient that you can check. Use the x value in A2 and compare your result with the y value in B2.
Stretch the answer from B2 all the way through B22. The $ signs in the formula ensure that the referenced cells do not move down the page as you copy the formula. The parameters should stay in the same place.
You should now have the x and y values needed for the graph (or chart as Excel refers to them). Select the range A1:B22 and insert a scatter plot. Excel should be able to make the graph with no further specification of domains or ranges and it will even add a title.
Change the parameters in some way. The power is a good choice. The graph should update immediately and you can notice how graphs with even powers will go in the same direction on both ends and how graphs with odd powers go in different directions. The macro will even give the graph a title that describes what it graphing. Is that slick or what?
Excel does provide an unnecessary legend to the graph. Select that and get rid of it.
Begin with the final touches: specification of additional terms. Copy your parameters in their four columns and a blank column after them (D1:H5) to cells starting at I1, N1, and S1. These positions are whether the x squared, x, and units coefficients will be calculated.
You may notice that when you copy the form controls, the Cell links that they include do not move along with them. When you click in one of the new check boxes, the original F5 toggles, no the value right beside the box. All of these links need to be updated to refer to the cells just to the right of the controls. Right click and choose Format Control... to do each one. Test them all.
All the parameters are ready to go, but the formula for y is not yet using them all. The formula in B2 now uses just $D$1:$H$5 for parameters. That range must be expanded to include three more parameters and the resulting formula is =Polynomial(A2,$D$1:$W$5). [Of course the formula needs to be copied down the column to all cells in B.]
You have just created your own graphing calculator. Congratulations! Show it off to your teacher. Make the fanciest curve you can and configure the chart with the fanciest "skin" you can imagine. Send it in for patent approval and worldwide distribution, etc.
If you finish this project way ahead of schedule, consider this question: can you make a calculator that plots a generalized sine wave with amplitude, frequency, and phase? The formula would be =amplitude*SIN(frequency*B2+phase) where B2 is a value for x. If you think you're up for it, give it a try. Sine waves are very useful in physics and engineering. Wikipedia has a reasonable introduction at http://en.wikipedia.org/wiki/Sine_wave. You can earn lots of extra credit with this.
Delivery
You should have a single Excel workbook file to either submit via email or a USB drive. Do so on or before the due date which will be announced in class.
Grading
Here are the items that will appear on the grade sheet. Remember that you can personalize your project in many ways as long as it doesn't interfere with these items. If it will, just ask first and perhaps you can go ahead anyway.
Call the file Polynomials
Label A1 and B1 x and y
Label cells D7 and I7 with these terms. Label N7 rows.
Enter values of -1, 1, and 21
Give these cells names of minX, maxX, and rows
Cell A2 =minX+(maxX-minX)/(rows-1)*(ROW()-2)
Stretch this formulas downward to A22
Label power, coefficient, max, min, and include
Insert a Spin Button
Specify Current Value of 3, Minimum of 0, Maximum of 10...
Add Spin Buttons for max and min.
Start out with of Current of 100, Minimum of 0, Maximum of 200...
Insert a Scroll Bar in cell E2
Format Current value of 5000, Minimum of 0, Maximum of 10000...
Lastly try out the Check Box control.
Format it with a Cell link of F5.
The top, in G1, can be set =F1
In G3 use =F3-100
Similarly in G4 use =F4-100
Just use =F5 in G5
Set G2 to =G$4+(G$3-G$4)/10000*F$2
Insert a Module
Enter the code in the handout
In cell B2 enter =Polynomial(A2,$D$1:$H$5)
Stretch the answer from B2 all the way through B22.
Insert a scatter plot
Change the parameters in some way
Get rid of unnecessary legend
Copy your parameters (D1:H5) to I1, N1, and S1
Choose Format Control... to do each one. Test them all.
The resulting formula is =Polynomial(A2,$D$1:$W$5)
Configure the chart with the fanciest "skin"