Examples of Good Practice in Earth Science
Learning & Teaching:

Spreadsheets

Illustrating Core Mathematics And Statistics With Excel
Graham Hendry (University of Birmingham)

Use Of Embedded Graphs In Teaching
Richard Treves (University of Sunderland)

Spreadsheets | Good Practice Keywords | Authors | Titles | Institutions | Home

Illustrating Core Mathematics and Statistics with EXCEL

Graham Hendry, University of Birmingham

Excel's "user friendly" interface, extensive charting facilities and mathematical and statistical functions (more than 100 in Excel 7) make it an ideal tool for teaching geo-mathematical modelling and statistics. One important feature that is often overlooked is Excel's ability to produce CAL (Computer Aided Learning) material using 'Form Controls' and Simple VBA macros. These can be used to help students, particularly those with limited mathematical experience, to visualise important concepts, either as part of spreadsheet exercises or as additional material made available, for example, via the School's Intranet.

A number of interactive worksheets were shown illustrating mathematical (solution of simultaneous equations, roots of equations) and statistical concepts (the normal distribution, linear regression). Scroller bars and buttons were used to dynamically alter data and, hence, to animate associated graphics.

Spreadsheets | Good Practice Keywords | Authors | Titles | Institutions | Home

The Use of Embedded Grpahs in Teaching with Excel

Richard Treves, University of Sunderland

One of the characteristics of current spreadsheet software is 'embedded' graphs. This means that with a program such as XL a set of data can be viewed as numbers and as a graph on screen at the same time. An example of a spreadsheet used for teaching the interaction of recharge and specific yield on the water table is given. Students manipulated the spreadsheet in a practical, the response from them was good, they picked up the concept well and also gained useful experience of dealing with computers for the first time.

Introduction
Embedded graphs in XL update themselves each time a cell entry is changed so that the effect of changing one of the numbers in the data set can be seen on the graph as soon as it has been entered. This characteristic can be put to good use in teaching by writing a spreadsheet that is a computer simulation of reality (e.g. a graph of water table height vs. time given values of recharge and specific yield). Students can change the variables (recharge, specific yield) and see the effect that such a change causes (increase in recharge will result in a water table rise).

Advantages
This type of practical has a number of advantages:

• A good technique to get over difficult concepts such as water table evolution with time.
• Serves as a good introduction to computers to computer illiterate students as there is very little to do other than load the spreadsheet onto the computer and change a few selected numbers.
• Introduces the concept of computer modelling, a difficult area to teach but one of great importance.

Examples
Three spreadsheets of this type have been developed by the author for use in the following subject areas:

1. Water table evolution with time given varying recharge rates and specific storage values
2. Water table evolution with time given varying recharge rates, specific storage values and permeability values
3. Apparent resistivity readings from a Wenner array given different layer resistivities and depths

Spreadsheets 1 and 2: Originally only one spreadsheet was developed for teaching this subject. However, students struggled with understanding the effect of the variables on the model if they were introduced all at once. They learnt better if variables were introduced separately, so the practical was expanded into 2 practicals the second leading on from the first.

Spreadsheet 3: This practical uses the embedded graph facility of XL but in a more realistic situation. The spreadsheet is a simplified version of current geophysical software where data is matched against models of the subsurface until a good match is found. Before this spreadsheet was developed proper geophysical software was used but the students spent too much time learning how to get the software to do what they wanted it to rather than exploring the concepts the practical covered.

Lessons Learnt

• It is important to explain clearly what the students will see on screen before they open the spreadsheet (e.g. What the axes mean, what the different plots on the graph represent)
• It is better to have 1 PC to 1 student so they can get 'hands on' experience of altering the variables and seeing the change.
• Students had a written handout that told them how to run the spreadsheet with spaces for answers on the same sheet.

With computer illiterate students it is vital to make the first practical they attempt achievable. If they have trouble with the first practical they tend to decide computers are 'hard to understand' and will approach later computer practicals with low enthusiasm.

Spreadsheets | Good Practice Keywords | Authors | Titles | Institutions | Home