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

Excel help with marking spreadsheet

Discussion in 'Assessment' started by NoNoNoah, Sep 28, 2011.

  1. NoNoNoah

    NoNoNoah New commenter

    Any Excel 2010 experts out there who can help me set up formulae that would convert test marks to equivalent values? e.g. a mark between 32-39 shows as 3B, 40-47 shows as 3A etc.
    I realise I will need to input the values but not sure where to start.
    Thanks
     
  2. I'm not the greatest expert in Excel, but try this.
    This example assumes that the mark is entered in cell A2. I doesn't matter as long as the formula is referencing the cell where the mark is.
    =IF(AND(A2>=32,A2<39),"3b",IF(AND(A2>=40,A2<47),"3a"))
    The bit in bold is the part you'll need to copy and paste between the external brackets, and you'll need to an external bracket for each condition you've added. (I think you can have up to 7 in a nested if function like this.)
    E.g. If I were to add another condition for 4c it would be
    =IF(AND(A2>=32,A2<39),"3b",IF(AND(A2>=40,A2<47),"3a",IF(AND(A2>=48,A2<55),"4c")))
    (NB - I made the marks 48 and 55 up for the last one.)
    Hope that makes sense.
     
  3. Have you investigated vlookup?
     

Share This Page