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

Data dilemma :(

Discussion in 'Science' started by Crookshanks, Jan 9, 2019.

  1. Crookshanks

    Crookshanks New commenter

    Hi everyone,

    I am posting on this thread as I could really do with some advice. In September I started a new job, which I mostly love, with more responsibility. One of my responsibilities is to create the spreadsheet for all mock results to be inputted which included the grade boundaries, etc.

    I can do the basic stuff but I am really struggling with it. When a formula for example is not working, I have no ideas why. There seems to be no one I can get for help on this. My head is dept struggles with it as much as I do. Other people within my department seem to be as clueless as me.

    The person in charge of data has been really unhelpful whenever I have asked for help in emails. Quick to say what I’ve done is wrong but offers my no help to rectify the correction. It’s also making me feel extremely incompetent, stressed and anxious.

    I’m contemplating going to see the SLT member in charge of cpd and asking if there are any cpd courses they can recommend for me to attend or if they can recommend a member of staff who could help me with this. On the other hand I’m worried I will appear very incompetent if I ask for help as it’s a new job which is a promotion.

    Please can you give me some advice as to what to do? Can anyone recommend any good books on excel for teachers of courses? I am based in the North West of England. Thank you
  2. averagedan

    averagedan Established commenter

    The problem with Excel is that it could be a number of issues, a plugin, enabled macros, etc., myexcel spreadsheet for the faculty I help manage contains hundreds of formulas/plugins/libraries and without sharing the spreadsheet (which would violate GDPR if it contains data structures) it's very difficult to give specific help. It's best to see someone else in your school who already uses the spreadsheet such as a department head, etc and ask them for advice. Sidestep the person causing the issue, then mention in passing that you solved the problem via another route to your line manager.
  3. ScienceGuy

    ScienceGuy Established commenter

    My advice on your spreadsheet is to keep it as simple as possible. The only formulas I use are simple ones like SUM and AVERAGE. I have used look up tables and conditional formatting in the past but find it easier to do certain things manually e.g. converting exam marks to grades, colour coding performance etc.
    Stiltskin likes this.
  4. particlezoo

    particlezoo New commenter

    I agree with ScienceGuy, keep it simple. If you want any help message me and I'll assist or you're welcome to have a look at the one we use, it's simple but does what you've described. If I have an issue I tend to ask either the IT technician or one of the computing teachers.
  5. AdamVero

    AdamVero New commenter

    Lots of great books out there, some quite expensive. I can highly recommend books by John Walkenbach "Mr. Spreadsheet" who has written loads over the years. Get one appropriate for your version eg Excel 2013 Formulas.

    Learn some of the real basics which are often overlooked in peer-to-peer "training" such as how and when to use $ in cell references in formulas to create absolute rather than relative references. When you copy a formula to another cell (or often, down a whole column), you may need some references to update but not others, and this is how you control that.

    In terms of direct help, there are of course many very helpful fora out there specifically for Excel issues with experts "waiting for your call". But realistically if you are trying to build things well beyond your own knowledge or skillset, this gets very slow and tedious very quickly. In the long term, an online or in-person training course would be much more effective - like having driving lessons rather than taking a cab for every journey.

    As to the problem at hand, can you describe what you are trying to achieve specifically (eg "find matching grade for a given mark") and what you have tried so far? I'm happy to help if I can.
  6. Crookshanks

    Crookshanks New commenter

    Thanks to everyone for all your helpful comments and replies. I don’t have an issue with inputting grades and working them out manually but the rest of the department want the spreadsheet so that it does it all for you, which is ok, I can just about cope with that.

    Thanks AdamVero I’m going to look into the book you have suggested. My main issue is exporting the data from sims so that if a student changes their surname or a set, my spreadsheet will also be updated. I don’t know if this there is a way to export data from sims so that any changes to data in sims will also be made in my spreadsheet? I think I exported the data from sims incorrectly. Tried asking the data manager for help if this unfortunately their response was unhelpful.

    Similarly in my previous school, when science teachers inputted data into the science spreadsheet, there was a way a column of data, for eg current grade, could be exported in one go into sims for a data drop, saving the science teachers’ time as they only need to input the data once then.

    This was not possible as the response I got was some of the names of students in my spreadsheet were wrong as well as the formulas but I have no idea why, other than I didn’t export the data from sims correctly.
  7. afterdark

    afterdark Lead commenter

  8. Skeoch

    Skeoch Star commenter

    Linking your spreadsheet to SIMS - I don't have recent experience of SIMS but in other systems you are better using the pupil number stored in SIMS (roll number or some such title) - these are unchanging in the database so name changes and spelling corrections don't affect data transfer. Equally you could use the UPN as a reference.
  9. Stiltskin

    Stiltskin Star commenter

    It's hard just to pick up someone else's work and fix any problems. I'd go for what @ScienceGuy says, start from scratch and keep it simple. Else take some time to work out how the previous person put it together and understand how each formula/macro they created works, using Google to help you.
  10. AdamVero

    AdamVero New commenter

    I can understand that feeling from others - after all the whole point of a spreadsheet (or any other tool like it) is to simplify and automate repetitive tasks to save time, remove boredom and eliminate human error. Ideally you want to aim for something like:
    Export report from SIMS.
    Copy and paste data to master spreadsheet.
    Make sure Excel has recalculated (look at bottom left of screen - automatic unless configured not to be).
    Refresh any PivotTables/Charts (if they are based on the same source data area you only need to refresh one to update the PivotCache which they all share)
    Use the results in whatever way you need, eg import back to SIMS

    You should be able to use VLOOKUP formulae to assign grades based on raw marks, as described here for example: https://exceljet.net/formula/calculate-grades-with-vlookup

    As others have mentioned, you need a unique identifier such as pupil number if you want to relate old data to new data, eg to average grades for the same pupil over time, or across subjects.

    A couple of things worth looking into:
    Use Named Ranges to make your formulae more readable, self-explanatory and therefore easier to troubleshoot.

    Use Tables (formerly called Lists) if you have loads of data which is then the source for something else such as a chart or PivotTable. If you add more rows of data to your Table they will automatically be included without having to remember to update ranges to include them, and any extra columns with formulas wil also be copied down. Formulas within Tables use a special notation which is more like using Names and easier to read.
    Good luck!

Share This Page