Beach Profiles in Excel
Producing 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 2012-03-27: updated spreadsheet with typos fixed (thankyou "make-up lady"), cells locked and column widths sorted.
You should be able to use the resources to draw profiles like the one below:
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...
The Resources:
Download and use. Let me know if there are problems with these...
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!
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.
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.