bodmas.org

MS Excel dynamic graphs

Filed in Maths, ILT on August 28th 05 .

Quadratic graph as drawn using the Excel spreadsheet with slider controls

Use the ‘forms’ toolbar in MS Excel to link a slider control with a cell. Then you can make ‘dynamic graphs’. Projected onto a screen, you can ask students to predict what the result of a change is going to be.

You can download a spreadsheet with the straight line graph, y = mx + c, and two forms of the quadratic graph, y = ax2 + bx + c and y = (x + p)2 + q. The second form of the quadratic helps to explain completing the square like problems, and helps to explain how transforming a graph by scaling in different directions can generate any quadratic graph from the basic y = x2.

  • Download the MS Excel spreadsheet
  • Download a screen video showing how to use scrollbars in Excel – from FERL at BECTa and produced by Alistair McNaught
  • Brief instructions on using Excel for learning – no pictures
  • How to build an interactive spreadsheet – 3Mb Word file, not evaluated yet, about using Excel in Maths teaching
  • The e-maths Web site looks interesting, and there is a whole page with MS Excel spreadsheets on Maths topics to download as well as a page about using interactive whiteboards .

I use the spreadsheet mainly on a projector in a brief whole class exposition with Access level 2 and GCSE classes. I’d set up (say) a straight line with a gradient of 2 and an intercept of 1, then ask

  • what will happen when I increase the gradient…
  • and when I increase the intercept….

Next, I have an OHP or a second copy of the straight line worksheet available with a very different graph and ask

  • How do I change the gradient and intercept to change the graph on the spreadsheet to look like the one on the OHP?
  • What direction and by how much

This all takes about 15 to 25 minutes with directed questioning, examples that include positive and negative intercepts and positive and negative gradients, and a recap. There is then a worksheet with 10 graphs on (copied from the spreadsheet into Word) and the students work in pairs to find the gradient and intercept from the graphs using a simple ‘x step = 1 triangle’ method for the gradient.

I find the projected spreadsheet helps me check the understanding, especially in the negative quadrants (directed numbers being a new concept for most level 2 students). The speed with which new graphs can be set helps people ‘see’ the effect of changes.

If you download the MS Excel spreadsheet here, you will notice that I have fixed the scale on the XY scatter graph that draws the graphs – the graph then moves around a window of definite size rather than having Excel recalculate the scales to fit the data.

Graph plotters like OmniGraph have more advanced and flexible facilities for exploring graphs, and allow you to change the graph formula on the fly, but tend to be harder to set up in my opinion.

This is filed under Maths, ILT. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are closed for this post.

[ bodmas home ]

Subscribe to bodmas.org using RSS

offline blogging tool ecto in use on bodmas

Site delivered by WordPress

Entries and comments feeds.

© Content Keith Burnett, 2006. This old school table-based layout was devised to support a Web authoring module. Mind you, it loads quicker than Kubrick in Safari...