KS3 Sats levels to decimals; how to do it.

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

1. Bob BridgesNew 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 )

Bob

2. valed

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 BridgesNew commenter

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

Thanks, Bob

4. strawbsEstablished 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 BridgesNew commenter

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

All the best, Bob

6. maths126New 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. valed

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

8. onelittlevictory

looks good, but I can't find the 'name box' bit?

I have found Name under Insert. Is this the right place?

9. valed

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. maths126New 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.

11. paul_mc

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. maths126New 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.
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
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. paul_mc

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

14. valed

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. maths126New commenter

Valed!
I can't believe I found something in Excel which you didn't know...

16. valed

Maths - there is LOADs!!! I am but a novice plodder

17. maths126New 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.

is a weekly publication of TheExcelAddict.com.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2