This page gives resources for the workshop activity in which a spreadsheet is used to explore some scientific ideas. Most of the examples are from chemistry and a few are from physics.
| Introduction - and what to do | What to do |
| Expressive example | Exploratory examples |
Essentially there are two ways in which pupils can use spreadsheets for modelling activities.
- they can write their own models - the 'expressive' mode of use;
- they can examine the models produced by others - the 'exploratory' mode of use.
Usually 'exploratory' modelling involves running a simulation - a model written by an expert - although this need not be the case. One useful classroom activity is to get children to produce their own models and then to explore each others.
In this workshop activity, you are asked to download, run and evaluate two models from those listed on this page - one exploratory and one expressive. These models are written in Excel and are based on mathematical relationships. However, there are other kinds of modelling systems available, based for example on rules. One of these is WorldMaker, which was initially developed at the Institute of Education by has been greatly enhanced at the University of Hong Kong.
There will only be time in the workshop for you fully to examine two models. I suggest that you first look at the 'expressive' example below and then chose one of the 'exploratory' examples (on a topic which interests you). For each of the models, discuss with others doing this activity how (if at all) you feel it would complement the teaching you already do; how it could be modified to make it more useful; and how, if you wished to use it in the classroom, you would organise this.
If you wish to examine other models listed on this page, you can return to this page after the conference - it will still be on the Internet.
Expressive example
A (technically) simple modelling activity that was devised for use with people studying 'A' level chemistry can be downloaded. This has been incorporated into both Nuffield and Salter's 'A' level chemistry courses to help students understand the nature of the equilibrium law and, by replacing boring repetitive but simple calculations, allows the pupils to concentrate on the science rather than the maths.
As was noted above, calculations are often a barrier to understanding in science. These barriers are of two sorts. The most obvious is where the calculations are difficult, but also just doing the same simple sum time and time again moves the pupils attention away from the concepts they are studying to the mechanics of the maths task. Automating the calculations allows pupils to concentrate on getting a 'feel' for what is happening - increasing their qualitative understanding of the process - which, contrary to most people's expectations is often the hardest thing for them to get. These examples are designed to illustrate ways in which the use of a spreadsheet can help overcome both of these kinds of barrier.
Entropy changes and the Haber process
The first 'exploratory' example allows pupils to explore the effect of changing concentrations and temperatures on both the position of chemical equilibrium and the equilibrium concentrations of the various reactants in the Haber process. It does this by calculating the entropy change in both the system and surroundings at any given extent of reaction - and hence the total entropy change and the position of equilibrium. The calculations involved are both difficult to understand and very tedious to do, yet the chemical ideas illustrated are both very important and often misunderstood.
Return
to the 'Exploratory' index
This spreadsheet uses a macro to run a simple Monte-Carlo simulation of the chemical reaction between 'a' and 'b'. The user sets the initial amounts of a and b together with the probabilities of a molecule of a changing to b and of b changing to a. The system then selects molecules at random and 'throws a dice' to decide whether they should change or not. Over time, the ratio a/b moves to be the same as that of the two probabilities - irrespective of the initial amounts of a and b present.
Two points:
- The macro and the 'display' sheet have been put in different workbooks - you will need to download both (preferably the macro first). Please feel free to put them together on your machine.
- This version uses Excel's original macro language.
Return
to the 'Exploratory' index
Strong acid-strong base titration
This example calculates the pH change as a strong acid is titrated against a strong base. Interestingly, although this is a very simple reaction, the mathematics involved in calculating [H+] is quite complicated and involves solving a quadratic equation for [H+] with complicated terms. If you wish to try working this equation out for yourself, the 'long' version of the worksheet gives the terms for a, b and c in the quadratic equation.
Return
to the 'Exploratory' index
This example uses a macro to calculate the pH of a solution as a weak base is added to a weak acid. (Load the macro before the 'Display worksheet.) The programme is general - the user selects all the values for the 'Initial Data' box. In this case, as there are three competing equilibria in the solution, calculating the concentration of Hydrogen ions involves solving a quartic equation - and quartic equations have no analytic solution. The function macro solves the equation using the Newton-Raphson iterative method. This would be beyond the capabilities (and inclination) of virtually all 'A' level students yet this method allows them to compare the results of a 'real' titration with that given by theory - and interesting and important concept.
Return
to the 'Exploratory' index
This was used in the Salter's 'A' level to help pupils calculate energy changes in reactions. The idea was to get pupils to work out which was the 'best' fuel for various purposes and by automating the calculations involved, concentrate on the chemical ideas.
Return
to the 'Exploratory' index
This has been used with younger (KS4) pupils to help them interpret the results of an experiment designed to calculate the formula of magnesium oxide.
Return
to the 'Exploratory' index
This example has been used with 6th form pupils - but is really a 'fun' exercise designed to show how systems with feedback can move to chaotic (but deterministic) behaviour. It was used to illustrate the difference between 'exactness' and 'precision' - a distinction pupils often have trouble appreciating.
Return
to the 'Exploratory' index
Angle of incidence vs angle of reflection
This was written for the Physics course of the Unify project in South Africa and allows students first to calculate the ration r/i; then sin(r)/sin(i); and finally to compare their experimental results with the predictions of theory - without having to do lots of simple but tedious calculations.
Return
to the 'Exploratory' index
Return
to top of this page
Back
to the home page for this site
| This page is maintained
by Tim Brosnan. Please send any comments to: t.brosnan@ioe.ac.uk
Last updated on 11th December 2001.
|