I.T. Skills in GeographyHYDROGRAPHS

For those studying Geography at Higher or Advanced Higher level, Excel can be used to quickly draw Stream Hydrographs, to show the relationship between rainfall and stream discharge in a particular river basin.

The technique and choice of graph type is the same as for drawing a climate graph, but instead of entering figures for temperature, you enter figures for river discharge.

1. Click on Microsoft Excel in All Programs to open a blank spreadsheet.

2. You will see the familiar table with rows running across the page and columns running up and down the page. Each box is known as a cell.

3. From the data sheet on the next page, you will see the days of the month are numbered from 1 to 31. We have to tell the program to treat these numbers as dates. To do this, and before you enter any data, highlight the cells in the first column, select Format, then Cells, then Text from the drop-down menu which appears. (see screenshot below)

1

DAY RAINFALL (mm)

DISCHARGE (cumecs)

2

1 0 3.82 12.5 3.13 34.5 24.94 23.5 31.65 5 29.66 0 18.47 0 10.68 0 6.69 8.8 4.910 1.3 4.411 1.7 5.812 0 4.713 5.8 5.414 0.5 5.515 0 3.916 0 2.917 0 2.518 0 2.119 7.2 1.820 2 1.921 20 5.722 0.4 6.423 0 4.724 0 3.625 0 2.826 0 2.227 10.3 1.928 2.2 2.229 12.9 8.530 9.2 7.331 1.2 10.9

3

Data table for River Echaig, near Dunoon, Argyll, January

1980

4. In the second column, add the figures for rainfall for each day from the data table on the previous page.

5. In the third column, add in the figures for discharge for each day from the data sheet. The data, as you can see, is from the River Echaig, in Argyll.

6. Your completed spreadsheet should now look like this

Note: You can enter data in the spreadsheet vertically, as above, or horizontally. In this example you could use the first three

rows or the first three columns.

7. With your cursor, highlight the three columns containing data.

8. Now select Chart from the Insert menu (or click on the Chart Wizard symbol) – see the screenshot above for where to find these commands.

9. Select Custom Types from the new window which opens, then scroll down and select Line–Column on 2 Axes.

4

Chart Wizard

Insert Menu

Highlight data

10. Click Next and Next again to move to this screen:

11. Click on Titles and add River Echaig Discharge January 1980 to the Chart Title box. Also add the words Days, Rainfall (mm) and

Discharge (cumecs) to the three boxes shown in the screenshot above.

12. Click on Legend and clear the tick in the Show Legend box; then click on Data Table and tick Show data table. Your graph should now look like the one below: if it does, click on Next.

5

Data Table

Titles

Legend

13. Now click on As new sheet and Finish to show your (nearly) finished graph.

14. If you have got this far – well done – you are now an expert hydrograph drawer! To make the graph look even more professional, you should now try the following improvements

(TOP TIP:- place your cursor arrow exactly over the feature you want to change and click on it – then figure it out for yourself!!!)

a) Change the colour of your river discharge line to RED

b) Change the weight of the line – make it thicker

c) Change the colour of the rainfall bars to BLUE

d) Close the gap between the bars to zero.

6

To return to the spreadsheet, select Sheet 1.

Calculations:

It is possible to perform a range of calculations on the data using Excel.In this exercise we will find: i) the total rainfall and ii) the average discharge.

15. Go back to the spreadsheet by selecting Sheet 1 from the graph – see previous page.

16. To find the total rainfall, select the first blank cell at the bottom of the second (rainfall) column.

17. Now double click (or single click, then press Enter) on the AutoSum icon – see below. The total rainfall will be instantly calculated for you! See below.

18. To find the average discharge, select the first blank cell at the bottom of the third (discharge) column.

19. Now select the little drop-down arrow immediately to the right of the AutoSum icon and click on Average. Then press Enter or Return and the average discharge will appear – saving you a lot of time!

7

AutoSum icon

Total rainfall

Average discharge

Drop-down menu arrow

Further Work:

1. Now try to draw a Hydrograph using the data in the table below.It comes from the textbook “Core Higher Geography” by MacLean and Thomson, page 69 and shows the data for the River Tay at Kenmore. If you use this textbook you may wish to try the questions on the same page, which includes data for Killin, further upstream.

Day Daily total rainfall at Killin

(mm)

Discharge (cumecs) at 0900 at Kenmore

01/01/97 0.2 13.5602 1.4 12.1403 0.6 11.5104 0.0 11.1005 0.0 10.5006 0.0 9.8207 0.0 10.1408 0.0 9.6209 0.0 8.9010 0.0 8.4611 17.0 8.1012 5.2 14.3113 2.2 38.6514 3.6 44.4515 0.0 48.3416 0.6 45.7717 4.6 41.0018 5.6 36.5419 0.0 37.4420 0.2 35.6521 0.0 33.5222 0.0 29.5523 1.8 28.1424 1.2 25.5725 0.4 24.4126 0.4 22.5727 0.0 20.1928 0.0 18.4429 0.0 17.0430 0.0 15.7531 0.6 14.69

8