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

Any Excel Experts out there?

Discussion in 'Personal' started by dominant_tonic, Jan 14, 2016.

  1. dominant_tonic

    dominant_tonic Established commenter

    I know very little about excel, so assume my knowledge level to be zero.

    What I want to do is this:
    1) My pupils work to a skill set, ticking off another skill when they have completed it a few times.
    2) These skills are linked to levels, though the children don't realise this.
    3) This is where excel would come in if I knew how to do it.

    1) The school works on a level basis, so I basically need to convert these skills into levels for data capture purposes.
    2) Can I set excel up, so that when I type in a certain skill it will convert it to the appropriate level for me?

  2. monicabilongame

    monicabilongame Star commenter

    The answer is yes, but exactly how it another matter. I would have to spend some time with the Dummies for Excel book.

    Usually it's the maths or IT teachers who can set up Excel to do the all-singing all-dancing stuff and I'm sure one will be along shortly.
  3. dominant_tonic

    dominant_tonic Established commenter

    Fab. Thank you - at least I know it possible. And I too, may invest in a dummies book.

    I am questioning the wisdom of attempting this setup, but music levels are so flexible, it is impossible for pupils to track their progress properly, so it would be lovely for them to be able to see their skills progressing rather than pointless levels. But I can't go through each child manually converting them - I teach too many classes. Hopefully this will work. Thanks for replying.
  4. grumpydogwoman

    grumpydogwoman Star commenter

    Sounds great! Excellent idea. And it would only need one of your colleagues to sit with you for ten minutes or so and you'd have it done.
  5. wanet

    wanet Star commenter

    Often created things like this. But would question that it has any real value.
  6. MrMedia

    MrMedia Star commenter

    You want something called Vlookup. It reads your cell content and matches it against a key elsewhere on the sheet and then inserts the level.
    A quick YouTube video will do that for you.

    As others have said, this bizarre converting of data into 'grades' (which no longer exist since we moved to norm referencing), for SLT is a relic which Ofsted will soon blow a cold wind over. Schools need to get to grips to a life without levels.
    The data is fine without the conversion to fit the data tracking software. Better for you to convert to whether they are making progress or not colour code.
    wanet likes this.
  7. grumpydogwoman

    grumpydogwoman Star commenter

    It has great value. The students can see what they've done. They can see what comes next. They can how quickly they're mastering the subject.

    If it's designed properly they can have a column for notes and comments and self-appraisal. It'll make them think. And it provides feedback for the OP.

    Frankly I don't give a tinker's cuss for the school's data but I do like the students to have an overview (and they'll get that) and their comments could inform my planning.
  8. wanet

    wanet Star commenter

    I have done it for vocational type courses. However, never liked levels may they RIP.
  9. magic surf bus

    magic surf bus Star commenter

    Depends how the skills convert into levels - is it based on strict numerical boundaries like percentages or marks? If so, the VLOOKUP (or HLOOKUP) functions are handy, as mentioned above.

    If you want to compile statements from a statement bank based on numerical data another option is CONCATENATE - I used this to compile reports many years before SIMS arrived.

    Third option - conditional formatting. Once you get a handle on this you can colour the cell background according to pre-set criteria, using a colour scale. For example, if red is selected for zero, and yellow is selected for full marks (or full skill set), a student making progress towards full marks will see the relevant cell coloured a sort of orange, eventually (one hopes) becoming fully yellow. I find this one quite handy for displaying sales figures in relation to average monthly sales.
  10. grumpydogwoman

    grumpydogwoman Star commenter

    All you have to think is this.

    Will I ever need to use this skill again?

    Because any Excel-type geek can do it for you in no time. Let them. Unless you really do want to know how to do spreadsheets.
    dominant_tonic likes this.
  11. scienceteachasghost

    scienceteachasghost Lead commenter

    I echo what some others have said that VLOOKUP is what you need here. VLOOKUP is one of the most useful features generally if you want to do your own data management! Online tutorials will guide you through it, failing that the school's data manager will be able to show you!
  12. dominant_tonic

    dominant_tonic Established commenter

    Thank you all. I will research the VLOOKUP and the Concatenate as well, as well as some of the other suggestions (like hoodwinking colleagues, but new to the school so perhaps not).

    It does have value. I cannot stand levels either, so vague as to be useless for me and the pupils alike. However, here in Wales, we are still stuck with them, and to that end that's how SLT need their data, understandably but annoyingly.

    So the skillset is fab I think. It will really allow pupils to see their progress, instead of random numbers which don't mean anything to anyone. I will be able to see at a glance what skills pupils are struggling with, and can think about how I deliver these lessons. It will be much more informative.

    Thanks all, I'll keep you posted.
  13. oldsomeman

    oldsomeman Star commenter

    Get the expert to show you dominant..they are not quite so easy.but are if shown and then you remember..or the experts does a sheet you can keep, you reuse then populate with the criteria and data.
  14. racroesus

    racroesus Star commenter

    Is the skill set ordered like levels?
  15. dominant_tonic

    dominant_tonic Established commenter

    It is ordered.

    It starts off with a very basic skill, and has maybe 10 skills building up to more advanced ones.

    eg. for performing, something like playing a series of notes with mostly correct rhythm for the first, then lots of others culminating in something like being able to perform a structured piece, with fluency and accuracy demonstrating control over basic and advanced techniques alike.


    Each of these skills are roughly linked to levels. Eg, skill 1 may be a level 3, with the last onhe being a level 8.

    Is that what you wanted to know?
  16. wanet

    wanet Star commenter

    You could also use an IF function. e.g. if if A, B, C and D are "Yes" then make Z "Yes" ELSE make Z "No".
    i.e. Level is only "Yes" when all skills are "Yes". If there are too many skills then you will find Arrays better.
  17. Didactylos4

    Didactylos4 Star commenter

    I can think of a dozen ways to do this including combinations of most of the suggestions above.
    As others have said though, unless this is a project the op wants to learn to create, it is just as easy to get a tame expert to create a workbook for you, especially if you want to change the level qualifications at a later date
    wanet likes this.
  18. oldsomeman

    oldsomeman Star commenter

    Why doesnt dominat ask you as you seem yo know what your talking about? :)
    I would sooner ask and if necessary pay someone than waste my time doing what i dont understand.
  19. wanet

    wanet Star commenter

    Getting what you require is usually best done face to face. It become a compromise between:
    What you want
    What can be done
    What you might want to develop it into in the future

    It is surprisingly easy for ideas not to coincide and once you see what can be done to develop it into something very different.
    Didactylos4 likes this.
  20. racroesus

    racroesus Star commenter

    I was wondering if a pupil could do skill 1 then skill 6 then skill 3 then skill 5 and so on or if they progress from skill 1 to 2 to 3 and so on. You really need to have discussions with someone who knows, as others have said.
    wanet likes this.

Share This Page