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.
  1. 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.
  1. 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:

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:

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”.

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:

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.

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:

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

correlate the constant coefficient with the “x” variable.

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:

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.

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:

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:

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:

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

graph:

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:

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

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

location you desire:

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.

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.

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

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.

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:

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:

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:

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:

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.

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:

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