That Blue Square Thing

Beach Profiles in Excel

Dunwich boardwalkProducing beach profiles in a spreadsheet is tricky. But, with a little formula magic, it can be done.

What I'm providing here is a spreadsheet to do the calculations for you (it will also do the cliff height calculation for you as a bonus). And there's a set of instructions for how to draw the profile.

Why haven't I done the spreadsheet so that it draws the profile automatically? I think you should learn how to do that bit yourself. There's lots of useful Functional ICT skills tied up in graph drawing and it's really not the hard bit.

New 2014-07-16: zipped version added to try to avoid the download problem some users have experienced. It only took 2 years...

You should be able to use the resources to draw profiles like the one below:

Beach profile diagram

I think you could take a diagram like this and add some useful annotations in textboxes (where's the storm beach?) and some photos to show the beach itself as well. That sounds like advanced ICT data presentation to me...

Use this scatter graph!The Resources:

Download and use. Let me know if there are problems with these...

MS Excel iconBeach Profile Spreadsheet

pdf iconProfile Drawing Instructions

zip file iconZipped version - this might overcome the persistent download issues that people have been having. The folder will need unzipping on a Windows system (right click > extract all). The spreadsheet and the instructions are included in one download. School networks can sometimes dislike zip files - you may need to talk to your system admin (and give them chocolates, cake or beer!)

Make sure you choose the right scatter graph! My choice would be the Scatter with straight lines and markers.

And do make sure you include the zeros in the graph so that the profile goes all the way down to the sea!

You might also be interested in the Kite Diagram Spreadsheet if you need to do things with vegetation cover transects.

Download problem: There seems to be a download issue for some users who appear to be downloading a set of odd files and folders rather than a single spreadsheet. Just contact me if this happens and I can sort you out with a copy of the file. I've added a zipped version which might solve the problem...

Technical Notes:

The spreadsheet is an Excel 2011 version. It should work in previous versions of Excel without a problem - but please let me know if it doesn't!

There are hidden columns on all the sheets. These hide the working out. You can unhide them if you want to and look at how they work - you can also accidentally delete them of course...

There are locked cells on the second and third sheets. This should stop people accidentally deleting stuff they don't need to delete. There's no password protection in place so full open-source ability to tweak as much as you want to.

The maths involved isn't that complex - it's simply using Sine, Cosine and/or Tangent rules from basic trigonometry. The complex bit is that Excel requires that angles are in Radians rather than Degrees before it will apply Sine rules and so on. I didn't realise that - which is why it took me about an hour to get this sorted!

The screenshots were done on a Mac I'm afraid so might not quite tally up. But you should be able to cope with that.