[sims] Dealing with BTEC National Diploma Grades in a marksheet

[tli]

We've got a sixth form Assessment Marksheet that counts grades.

 

A level grades are easy- we just use GRADE TALLY COLUMNS to count each of the grades. The problem comes with BTEC National Diploma where the grades are DDD, DDM, DMM, MMM, MMP, MPP, PPP, PPU, PUU, UUU. We want to count each of the individual Distinctions. Merits and Passes eg DDM counted as 2 Distinctions and 1 Merit.

 

I had thought that I could use a separate NESTED IF THEN ELSE for each of the vocational subjects to count each of the Ds, Ms and Ps with something like this-

 

IF grade=DDD THEN 3 ELSE IF grade=DDM THEN 2 ELSE IF grade=1 THEN 1 ELSE 0

 

but the function doesn't seem to work with marksheet grades.

 

 

Currently, I've had to use a number of GRADE TALLY COLUMNS to count the combinations of grades but this involves using 31 hidden columns to achieve the count!!

 

Anyone got any better ideas?

[CAM]

I'd collect three separate columns, one for each grade, it saves a lot of complexity in the long run!

[tli]

I'd collect three separate columns, one for each grade, it saves a lot of complexity in the long run!

 

The problem is that all I can count are the 10 separate grades. They appear as DDD, DDM, DMM, MMM, MMP, MPP, PPP, PPU, PUU, UUU rather than separate Ds, Ms and Ps. There doesn't seem to be a way to count the Ds in, for instance, DDD or DDM.

 

Counting DDD gives 1 not 3 so my solution at the moment is to have 3 separate identical columns counting the Ds in DDD so that I can SUM them to get an answer of 3 then 2 to count the Ds in DDM and so on. This requires 31 columns to cover every eventuality

[ICTLady]

Do you use these grades elsewhere? if not, I think I would do what @CAM is suggesting and have three aspects called BTEC Grade 1, BTEC Grade 2 and BTEC Grade 3 and get teachers to put each of the three elements into a separate aspect.

 

Either that or export the marksheet, use a few formulae to separate the grades in Excel into three separate parts. Then either count them in Excel or import them back into hidden aspects each containing one single-letter grade. Then run the GRADETALLY.

[Oddbod]

Have a look at this for stripping the three character string into columns, then you only have to apply a four state if, then else statement. This will even capture the grade permutations that you haven't listed…[emoji3]

 

http://www.techrepublic.com/article/save-time-by-using-excels-left-right-and-mid-string-functions/

 

And this should explain why your nested if statement didn't work

http://spreadsheetpage.com/index.php/tip/alternatives_to_nested_if_functions/

 

This should reduce you down to five columns:

A1= input

B1= 1st char stripped with case statement applied to test (D,M,P,U)

C1= 2nd char stripped with case statement applied to test (D,M,P,U)

D1= 3rd char stripped with case statement applied to test (D,M,P,U)

E1= sum of B1, C1, D1

 

You could even reduce to two columns of input and output later…

Edited February 3, 2015 by Oddbod
a bit more though will get it down to two columns

[tli]

Thanks for all the suggestions.

 

I can achieve this easily in Excel but wanted something using a SIMS Marksheet.

 

I have since discovered that the reason that my NESTED IF THEN didn't work-

 

IF grade=DDD THEN 3 ELSE IF grade=DDM THEN 2 ELSE IF grade=DMM THEN 1 ELSE 0

 

was that the test was on the points value rather than the grade itself. I've now managed to get something working though it still needs lots of hidden columns to cover all subjects and all grades.

[tli]

I had previously achieved a solution using GRADE TALLY COUNTS but this needed over 30 columns!

 

For instance, I had 3 columns counting DDD, 2 counting DDM and 1 counting DMM. I then SUMmed up the result of these 6 columns which gave me the number of individual Distinctions. When I had added all the necessary columns to cover all the possible grade combinations, I ended up with over 30!