Tutorial for Solution-Type Interactive Tool



Tutorial for Solution-Type Interactive Tool

1. PURPOSE: The following tutorial is designed to allow students and faculty, with minimal Microsoft EXCEL experience, the ability to create similar tools or adapt the tools already created by Bart Stewart and Jeff Libby.

2. METHODOLOGY: This tutorial will discuss in detail the steps to create the tool and will utilize “screen shots” from Microsoft EXCEL to show the menus needed to create the various components of the interactive tool.

3. INTERACTIVE TOOL DEVELOPMENT:

a) In the solution-type interactive tool, we are developing a program to visually show the student the three types of solutions to a system of two equations. The tool is designed to show the graphical solution as well as stating what type of solution (unique, infinite, or no solution) results. Furthermore, it helps to reinforce the concept of reduce row echelon form (RREF) by displaying the matrix solution for RREF and also including the general form of the matrix solution for each type of solution.

b) The following are detailed steps in developing the tool:

1) The first step is to set up a system of two equations as seen below:

[pic]

The equations are set-up with additional columns between the variables x

and y in order to allow follow-on manipulation of the values of the sliders.

The sliders are utilized to change the constant coefficients for each

variable. Now that the basic system is set-up, we can then insert the sliders by utilizing the following commands: View, Toolbars, Forms. These commands open the menu to choose what type of slider the user desires.

The following screen shot shows the commands for opening the menu to obtain the desired slider:

[pic]

With the slider chosen, we can now assign properties to the slider in order

to create the range of values for our constant coefficients. Cell “B2”

below will be used as the range for our slider in order to adjust the

constant coefficient in Cell “A2”.

[pic]

This interactive tool utilizes constant coefficients from –10 to 10

(excluding zero). Zero is excluded as it creates problems in the RREF

computations. In order to create this range, we utilized a conditional

statement for cell “A2”. The conditional statement utilized allows the

range of values in “B2”, which are controlled by the slider from 0 to 20, to

be manipulated to the desired range of -10 to 10. The following shows

the conditional statement used:

[pic]

The same process is done to create the constant coefficient in Cell “A5”

for the variable “x” in the second equation. The slider is now linked to

Cell “B5” and the conditional statement in Cell “A5” controls the desired

range of the constant coefficient.

[pic]

Now that the constant coefficients for the “x” variable have been created,

Column B can be hidden so that the information is not seen by the user.

This is accomplished using the following commands:

[pic]

The following is a screen shot of Column “B” hidden. This allows one to

correlate the constant coefficient with the “x” variable.

[pic]

The same process is now conducted to create the constant coefficients for

the “y” variable in both equations. The process utilizes Columns D and E

in the same manner discussed above. This time Column E will contain the

information that will later be hidden from the user.

The following is the result of creating the sliders for the constant

coefficients for the “y” variable in each equation:

[pic]

The next step was to develop the values for the right hand side of the

equality. This process is similar to the development of the constant

coefficients.

[pic]

In this process, Column “H” is controlled by the sliders in Column “G”.

The command in “G2” and “G5” are utilized to create the range of our

constants. The range for the constants is from –10 to 10 (zero does not

create a problem in this case as a result you do not have to use a

conditional statement).

A color scheme was now created to delineate between the two equations

for later use in creation of the graph. The following commands were

utilized to open the command bar to change the font color and provide

background fill colors:

[pic]

2) The next process is to create the interactive graphical solution. The first

step is to create a column of values for the “x” variable (in our model the

range for the “x” variable was –20 to 20). This column was utilized to

solve for “y” in each of the equations separately. (NOTE: We are not

solving the equations simultaneously at this point to get a solution) The

following screen shots show the columns of data built and equations used

to compute the data:

[pic]

[pic] [pic]

Now that the data is created, the interactive graphical solution can be

developed. This is accomplished by first highlighting the data in columns

K-M and utilizing the graph toolbar:

[pic]

The next several screen shots take you through the development of the

graph:

[pic]

Prior to going to the next menu, ensure to enter the series information by

selecting “Series” at the top of this window. It will bring up the following

window to enter the required information:

[pic]

The next step is to assign the title and axis labels:

[pic]

The last step in the “Chart Options” allows you to place the graph in any

location you desire:

[pic]

For our model, we just placed it in the same working sheet. If the colors

of your lines are not what you desire, they can easily be changed by

selecting each line separately and choosing the color you desire. This is

done by selecting one line using the left mouse key and then using the

right mouse key to get to the appropriate menu.

[pic]

[pic]

The final product is an interactive graph. The graphed line solutions are

linked to the sliders and will change as the user changes the constants in

the system of equations.

[pic]

3) The next process is to develop the Reduced Row Echelon Form matrix in order to determine the type of solution: Unique, Infinite, or No solution. Furthermore, if the solution is unique, the tool will determine the unique “x” and “y” variables. This entails several conditional statements and is the most difficult part of developing this interactive tool. Before developing the RREF matrix, the model will determine the type of solution using conditional statements.

The column below called “Common Points” is used to determine when the

“y” values are equal in both equations

[pic]

[pic]

With the above information, we are now able to develop a conditional

statement to determine the type of solution. The following screen shot

shows the conditional statement command to determine the type of

solution.

[pic]

Now that the type of solution has been determined, the model now

develops the RREF matrix to show the corresponding form and also

determines the actual values for “x” and “y” for a Unique Solution.

The first step in this process is only cosmetic (ie: creating the matrix

without any consideration of conditional statements). This was

accomplished using the commands Insert, Object, Equation Editor 3.0:

[pic]

[pic]

Now that the cosmetic matrix brackets are set, you are ready to build the

2x3 RREF matrix. The first column is the same for all three solutions and

is created by simply typing in a “1” and “0” into the appropriate cells. The

remaining four cells required conditional statements and will be explained

in detail below.

We will first discuss the cell in the first row, second column. This value is

determined by the following conditional statement:

[pic]

This conditional statement allows the cell in the first row, second column

to take on the appropriate value for RREF.

The next cell to determine is the second row, second column. This value

is determined by the following conditional statement:

[pic]

The next step is to determine the value for the first row, third column.

This step requires an extensive conditional statement for the unique

solution. Since this cell is the solution for the “x” variable, one must first

solve one equation for “y” and substitute into the second equation in order

to solve both equations simultaneously for “x”. This command is shown

below:

[pic]

The final part of the RREF matrix is the second row, third column-“y”

variable solution. Once again the equations must be solved

simultaneously. This is accomplished by solving the first equation for “y”

and utilizing the unique solution value for “x” to determine the unique

solution value for “y”. If the solution is not unique, then other conditional

statements are utilized for an Infinite Solution and “No” solution. The

following screen shot shows the conditional statement used for this cell.

[pic]

This completes the difficult part of setting up the interactive tool. The remaining parts are purely cosmetic and are up to the users discretion. One can hide information not needed to be seen, color code as desired and add in other useful information. For our tool, we added in the standard RREF form for each of the three types of solutions, rearranged the information into a user friendly template, and added several labels to identify the results. When you are all done your model may look like the following:

[pic]

Prepared by MAJ Jeff Libby and CPT Bart Stewart, June 2002.

-----------------------

This is the slider mechanism chosen for this interactive tool.

Cell “B2” is the range of our slider

Range of slider can go from 0 to 30000. Must manipulate in order to get desired values.

Can increment sliders by integer values only, but can manipulate to increment by desired values.

Right click on slider and chose Format Control.

Links slider to desired cell for incremental counter.

First command eliminates problem created by zero

“B2” refers to the cell that the slider controls. Ten is subtracted to get the desired range for our coefficients.

The line reads, if B2-10=0 then the constant coefficient is B2-9. Other wise, the value is Cell “A2” equals B2-10.

These commands will hide any desired columns that you do not want the user to see. Can easily “unhide” at a later time to adapt the file for other use.

Information in Column “B” hidden.

Each slider controls the range of the constant coefficient in the cell directly above each individual slider.

Provides various background fill colors.

Provides various font colors.

Command to increment the values for “x” by 1 from –20 to 20

Command to solve for “y” for the blue equation.

Command to solve for “y” for the red equation.

Data used to create the graphical interactive tool.

Toolbar for Graphing Command

Utilized Line Graph and sub-type shown.

Ensure the Data Range includes all three columns of data.

Enter the name of the series and ensure the “Values” are correct.

The blue equation is already done and can be verified by the name change in this window.

Ensure to list the range values for the “x” variable or your values on your graph will not correspond.

Assigns name to graph and to the different axes.

Desired location identified in this block.

Right mouse key to get this menu to open and then choose “Format Data Series” to change color of line.

One can also change the source data and various other options from this menu. You can also highlight the entire graph with the left mouse key and then use the right mouse key to change desired data.

Choose your desired color. In this case we would choose blue to match our color coding from earlier.

Conditional statement identifying if Column L is equal to Column M. If they are equal then TRUE, otherwise FALSE.

Number of common is counting the number of TRUE statements from previous column. This statement says to increment the counter if column N has a TRUE statement.

Ratio for Blue and Red are just the ratio’s of the “x” constant and “y” constant. The use of these will be discussed below.

The command states: if all conditional statements are TRUE then we have infinite solutions (ie: same line), otherwise check the ratios. Having the same ratios means that the constant coefficients for the “x” and “y” variables are the same thus the only difference is a different y-intercept which leads to parallel lines( NO Solutions. If neither of these conditions are satisfied then there is a Unique Solution.

Select Insert and then scroll down to select object.

Select Microsoft Equation 3.0 in order to build outside brackets of the matrix. May have to manipulate to desired size.

Select Ok to bring up the following toolbar

Select desired objects for brackets

The cell “Q10” is the dummy location for the type of solution from the above steps.

This conditional statement assigns the value of zero to this cell if the solution is unique. If it is not unique, the value is simply the quotient of the constants for the “y” and “x” variable. Simply dividing through the first equation by the constant associated with the “x” variable.

Again, Cell “Q10” is referenced. If the solution is unique then a “1” is assigned to the second row, second column. Otherwise, a “zero” is assigned as required for the correct form for an infinite solution or “no” solution.

Once again this command references the cell “Q10”. If “Q10” is a unique solution then the “x” variable that solves each equation simultaneously is given by the above statement. If the solution is not unique, then the variable is determined by dividing the right hand side of the equation by the “x” variable constant.

If “Q10” is “Infinite Solutions” then this cell will be assigned a value of zero.

If “Q10” is “Unique Solution” then this cell will be assigned the value computed by the above statement.

If “Q10” is “No Solution” then this cell is simply the difference between the right hand side values .

Cell “F16” is the unique “x” variable value. Computed by the statement shown on page 13.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download