Quadratic Equation Graph in Excel

The Assignment:

Design an Excel spreadsheet to graph any quadratic equation

of the following form:

y = Ax2 + Bx + C

• A, B and C are constants that are inputs to the spread sheet
• The domain is also an input to the spread sheet in the form of minimum and maximum x values
• The graph should be made of 100 (x,y) points
• The graph is "live" - it should update anytime an input is changed

You can add cell borders here

Split the screen:

• Go to View - Split to add split lines, remove the vertical split by dragging it to the edge.
• On older versions of Excel:
• Make sure a blank cell is selected
• Place the cursor over the top right of the sheet
• The cursor will turn into parallel lines
• Hold down the mouse button and drag the split down

The top of the spread sheet is for information and input data

It includes a title and the general form of the equation.

The cells that are outlined are where the user inputs the values for A, B and C as well as the minimum and maximum x values.

Generate the Graph

• Highlight the cell with the first x value
• Hold down the shift key and click on the last y value, this should select all the data points
• Insert an xy scatter chart

Generating the data points

The theory of generating the data points is simple:

Pick 100 x values and then find the corresponding y values

The worksheet is split.

This allows you to see the top and bottom of the columns and avoids haveing to scroll.

The x values are generated by starting with the minimum x value and then adding 1/100 of the distance between the maximum and minimum x values to each successive value. On the spread sheet the first x value is the minimum x value. In the cell for the second x value write a formula that adds 1/100 of the distance between maximum and minimum to the previous x value. Then copy the formula down.

The y values are generated by taking each x value and running it through the equation to generate a corresponding y value, resulting in an (x,y) ordered pair. after writing a formula for the first y value copy the formula down.

To make repetitive calculations easier, Excel allows you to designate cells with a name. For example select cell B3, then go to Formula - Define Name and call it a. Cell E3 is b, H3 is c_ , C5 is Xmin and F5 is Xmax.

In older versions of Excel:

In order to properly generate the data points relative and absolute references are used. A relative reference will adjust or index itself when it is filled down, while an absolute reference does not. Excel assumes all references are relative unless a $ is placed in front of both parts of the reference. For example E7 is a relative reference, while $E$7 is absolute.

Formula examples

For the x value in cell A109:

=A108+((Xmax-Xmin)/100)

For older versions of Excel:

=A108+(($F$5-$C$5)/100)

For the y value in cell B109:

=a*A109^2+b*A109+c_

For older versions of Excel:

=$B$3*A109^2+$E$3*A109+$H$3