That Blue Square Thing

Applied ICT A Level Unit 10 - Advanced Spreadsheets

Note: this page applies to the AQA Applied ICT A level specification. This exam was withdrawn in 2012, with final exams in 2013. The content will be retained as an archive and because it has some stuff that might be quite useful for someone or other
If you're looking for examples of spreadsheets, including exam marksheets and controlled assessment timekeepers, then scroll down the page. They're still here, but I'm using this page to do some of that teaching stuff as well...

You will need to be able to produce a reusable spreadsheet system for a client of your choice. Your system will need to include at least five advanced spreadsheet features to help meet the specific needs of the user. You need to be able to do this, and document it, in a 20 hour exam period.

PDF icon Checkout the list of advanced spreadsheet skills. Using five of these is pretty easy actually

Sections A to F are completed before the 20 hour exam period. These make up 32 of the 70 marks available for the unit. Each section has it's own page to guide you through the things you need to do to hit the marks - the nav bar will take you there.

This page has some testbeds and trial solutions to work on specific spreadsheet issues. Some of them may be helpful.

The Exam Marksheet Examples:

Excel file logoA simple exam grade boundaries spreadsheet (updated: now it copes with A* grades properly!)

This uses data transferred from another sheet, lookups, validation, customised error messages, error trapping, nested if statements, the use of named ranges in formulae, protection and hiding. In other words, at least 7 advanced skills from the exam board list as well as a user interface which meets the needs of the users.

Excel file logoAnd a slightly tweaked version to show a different way of doing it (also now updated to get A*'s right)

This is based on a version developed by someone on the Staffordshire Learning Net Geography forumExternal link icon. I've adapted it to use named areas and to calculate the difference in grades. It uses lookups, error trapping and named areas, but could easily be developed to include protection and so on.

The Controlled Assessment Timekeeper:

Excel file logoA way of adding up the time students spend on controlled assessment tasks

This is simple enough. Just enter the names down the left and then put the minutes they spend on the task each session in the boxes (you could add a row to put the date in easily enough). The total time gets sorted by a use of =SUM(C3:V3)/60/24 and then formatting in [h]:mm format (use the Custom format option). This can be improved - I'll be seeking feedback on what people want it to do, so by all means let me know!

Excel file logoAnd a version which allows the amount of time spent at High and Low levels of control to be recorded

This is protected to remove the risk of accidental deletion of stuff, but there's no passwords involved so the protection can be removed if necessary. There are 20 sessions available. More columns could be added, but the High and Low sheets would also need more columns adding - I can do this for anyone who specifically needs it is they let me know!

Please provide feedback if there are problems with the sheet or if there's anything specific you want it to do - if you need me to explain how it works I'm happy to do that as well, although I can't promise that it'll be clear and coherent or anything...

The Animal Feeds Example

A problem came up. Someone wanted to make the contents of one drop down dependent upon the value selected in the previous drop down. Hmm - tricky.

But, actually, not that tricky - it simply requires a bit of Lookup madness!

Excel file logoA sheet to demonstrate how to make one drop down dependent on another

This is a bit complex. Feel free to ask me to go over it in more detail!

The Order Number Example:

Excel file logoA sheet to demonstrate how to produce an updated order number

This showcases a few basic (but effective) moves - how to get the current date in the sheet, how to calculate VAT using a named cell, how to (in theory) produce a macro to copy order details into a filing sheet and how to produce an order number that's unique to the order being dealt with (basically using a primary key as you would do in a database).

The order number was the main reason for producing it, to demonstrate one way of doing that (which is harder than it looks and involves a touch of concatenation as well!). There may well be other, funkier ways of doing this (including using VBA I know), but I wanted a nice simple method for this one. If you know a cooler way of doing it, please let me know about it!

Check it out - the sheet doesn't have any macros in it but is fully annotated.

The macro code for the copy, pasting and deleting macro would probably look something like this:

macro code for order sheet macro

It's actually quite important to start recording it from the Order Details sheet so that you get the first line occurin' - otherwise when you run it the macro will insert the cells in the sheet your currently in (almost certainly the Order Details sheet) which would be bad.