1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded education professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Spreadsheet Analysis

Discussion in 'Assessment' started by dixonh, Aug 20, 2018.

  1. dixonh

    dixonh New commenter

    Hi everyone, I teach nearly 500 students per week and I am struggling to put personalised strategies in place for students because identifying the ones left behind, key groups, etc is becoming too time consuming.

    I want to create an excel document that identifies if students are on, below or above their target so I can use this data in my planning. I have tried to create formulas in excel but it doesn’t always work due to the nature of the grading system (we use 1-9 for KS3&4 but at KS3 students start off on TR which is pre grade 1).

    Is there anyone who already has a spreadsheet that works out:
    - Attainment against target, preferably colour coordinated
    - Attainment of key groups against targets

  2. Flanks

    Flanks Senior commenter

    You could navigate the TR problem by giving it a value of nil when you input in the spreadsheet.

    What colour coding do you want? Conditional formatting is quite straight forward if you want it to change colour for higher/same/lower than expectations. Simply have a cell value for what is expected, then the formula can compare to actual assessment and change colour. This search should give you the methods involved which are quite straight forward: https://www.google.co.uk/search?q=colour+coding+with+conditional+formatting
    border_walker and dixonh like this.
  3. sckinsley

    sckinsley New commenter

    I agree with Flanks, if you use 0 rather than TR the formulae become very straight forward. You say that you have almost 500 students a week, presumably you teach a subject which probably has only one period a week and lots of groups. If this is the case I would suggest that you have a look at the Information Management System your school uses and if possible work through that. The reason for this is practical. An Excel document will quickly become out of date with student and group changes and it is a pain to update. Using the IMS (I have created marksheets in ours, we use SIMS) the updating of students and groups becomes automated and the formulae only have to be created once and will calculate in real time. The process will vary with the IMS but may be a far simpler way.

Share This Page