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 professionals and have your say on the issues that matter to you.

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

    Dismiss Notice

KS3 Sats levels to decimals; how to do it.

Discussion in 'Mathematics' started by Bob Bridges, May 7, 2009.

  1. Bob Bridges

    Bob Bridges New commenter

    We are doing SAT papers with our Year 8 as end of year test.
    I would like to give them results as decimals not sub levels.
    Any ideas how ?
    ( Have searched this forum already )
    Thanks in advance,

    Bob
     
  2. Yes, Bob.

    And if I can find the spreadsheet i did this on last year, i'll let you know.
    Basically, i set up a lookup table for the level boundaries and interpolated the result to decimalise it.
    When do you really need it by?
    Cheers

     
  3. Bob Bridges

    Bob Bridges New commenter

    Thanks Valed.
    Don't rush to find it - just when you have time.

    Thanks, Bob
     
  4. strawbs

    strawbs Occasional commenter

    I do it with a series of hideous looking nested if statements that, whilst hideous, do work really well and simply once entered.
    The lookup way is far easier on the eye though!
     
  5. Bob Bridges

    Bob Bridges New commenter

    Valed ( and everyone else),
    Many thanks.
    Knew I could rely on you.

    All the best, Bob
     
  6. maths126

    maths126 New commenter

    We use the INDIRECT function which permits some quite neat coding.
    First create a worksheet which only contains LOOKUP tables such as
    0 4c
    11 4b
    23 4a
    30 5c
    Highlight the cells from the 0 to the 5c and call this LevelsT1 as a "named range" in the NAME BOX
    The next one is similar
    0 5c
    10 5b
    15 5a
    20 6c
    25 6b
    But this one is called LevelsT2
    Notice that the names are the same apart from the number.
    If a student sat the 4c to 5c paper then their tier is 1
    If a student sat the 5c to 6b paper their tier is 2.
    Let's assume that the child's name is in cell A2, their tier is in cell B2 and their raw score is in cell C2.
    Watson, Wayne 2 23
    In cell D2 we have a formula
    =LOOKUP(C2,INDIRECT("LevelsT"&B2))
    which is interpreted as
    =LOOKUP(C2,LevelsT2)
    and Wayne's level is correctly given as 6c
    Change his Tier to a 1 and watch his level plummet to a 4a.


     
  7. Maths !

    THAT is very neat. Certainly MUCH slicker than my current version.
    I'm gonna play with that if you don't mind.
    Happy Bunny am I [​IMG]
     
  8. looks good, but I can't find the 'name box' bit?

    I have found Name under Insert. Is this the right place?
     
  9. Well, you can do it that way
    BUT
    at the top left side of the screen on the end of one of the toolbars, you should see a box with the current cell address.
    If you select a range of cells with the cursor, you can name that range by typing a name in the above box.
    Cheers
    Val
     
  10. maths126

    maths126 New commenter

    If anyone wants to try this but can't get it to work, feel free to send me a copy of your spreadsheet to look at and (hopefully) fix. It's such a timesaver that I would be only too happy to share.
    Usual MathsIsFun address. [​IMG]
     
  11. Can anyone help?
    Tried the lookup function =LOOKUP($AP5,AW5:AW13,AX5:AX13)
    This return the correct grade for cell AR5, but when I do ctrl d to copy the function excel changes the $AP5 (which I want it to do) and the 5 and 13 (which I dont want it to do)
    Probably havent described the problem clearly, but are there any mind readers who know how to copy a function without excel changing some of the cell references?
    Thanx
     
  12. maths126

    maths126 New commenter

    You seem to be part way to the slution already, Paul.
    For an ABSOLUTE (fixed) reference, put a dollar in front of it.
    $AP5 fixes the AP but not the 5
    AP$5 fixes the 5 but not the dollar
    $AP$5 fixes both
    For a relative (moving) reference, leave the dollar out.
    To keep the formula simple, I really do recommend the use of NAMED RANGES.
    <u>How To Create And Use A Named Range</u>
    For tidiness, I usually keep all my named ranges on a separate tab (worksheet). They can be used in any other worksheet formula in the workbook because Excel "knows" where they are.
    Another tip I have is to colour the named range and write the name of it below the coloured zone. This helps enormously in error-checking.
    A range can be anything from a single cell to the whole worksheet.
    I usually use n by 2 tables.
    Let's say you have a list of threshold marks and their corresponding levels or grades.
    For example,
    0 U
    35 D
    45 C
    55 B
    65 A
    80 A*
    NOTE THAT THE LEFT COLUMN MUST BE IN ASCENDING ORDER.
    Now highlight everything from the 0 to the A* (you might as well colour it at this stage) and then go the the Name Box to the left of the formula bar. It will probably say A1 or B3 or something.
    Click inside the name box and type the name you want to use for this table.
    The name must start with a letter but contain no spaces. Let's call ours Test3Grades
    In the row below the table, I type Test3Grades again, to remind myself what I called it.
    Now I go back to the worksheet containing my Test 3 marks
    Let's say these marks are in column P and the first one is in column P2
    I create a new column, Column Q, for Test 3 Grades.
    The formula for cell Q2 is then
    =LOOKUP(P2,Test3Grades)
    I then fill this down and every student's grade is filled in.
    Example worksheets for this technique, and others are on my Excel Tutorial Resource:
    https://www.tes.co.uk/article.aspx?storycode=3003350


     
  13. Cheers maths 126
    I sorted it out by the try and fail method!
    I will try your way thanks.
     
  14. Maths 126

    I DID try your INDIRECT method and it works a treat but I still need to use my present method to find the level boundaries for the decimalisation by interpolation so have left sheet unchanged.
    Thanks for the tip - I WILL be using it elsewhere.
    Cheers
    Val
     
  15. maths126

    maths126 New commenter

    [​IMG] Valed!
    I can't believe I found something in Excel which you didn't know... [​IMG]
     
  16. Maths - there is LOADs!!! I am but a novice plodder [​IMG]
     
  17. maths126

    maths126 New commenter

    This is not a commercial plug at all - just one Excel addict showing another where to get a fix...
    Francis Hayes (The Excel Addict) is a great guy and his free weekly Newsletter is always a good read. From the footer of the last issue:
    SUBSCRIPTION INFORMATION
    Spreadsheet Tips From An Excel Addict is available only to subscribers of my email newsletter. My records show that Maths126 at mrf@mathsisfun.net requested a subscription to this newsletter at TheExcelAddict.com on September 9, 2006.

    If this newsletter was forwarded to you and you would like to get your own copy, please visit http://www.TheExcelAddict.com

    "Spreadsheets Tips From An Excel Addict"
    is a weekly publication of TheExcelAddict.com.

    Copyright Francis J. Hayes All Rights Reserved.
    8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2
     

Share This Page