Evaluate student marks with respect to grade

**emu85** · 07-09-2013, 11:31 AM

I'm putting a spreadsheet together to track coursework marks for my GCSE students. Pupils complete three tasks, receiving a grade for each individual task and then an agregate grade for the complete portfolio. Thus far, I have conditioned cells so that staff cannot imput marks outside of the correct range for the relevant markschemes. I have also imputted formulas so that the spreadsheet will calculate the total mark for each individual task and the portfolio as a whole. Finally, using Vlookup the spreadsheet can work out the grades for each component and the portfolio overall.

What I would like to be able to do is have the spreadsheet figure out

A) Is the pupil above, below or on target to hit their expected grade
B) How many marks they need to meet their target grade.

As an example, Pupil A's predicted grade is in cell d3, his total mark for task one is in h3, whilst cell i3 contains his grade for the first task. I would like cell j3 to say whether this is greater/less than or equal to the target grade in d3 (a +/-/0 system would work). I would like cell k3 to figure out the difference between his total score for the task, and the grade boundary for his target grade (ie how many marks he needs to earn over the remaining tasks to ensure he hits his target grade (data is included in Vlookup table I mentioned previously).

I've driven myself round in circles trying to create if/and formulas, but am ready to admit defeat and accept that I need more expert help. If anyone can shed any pearls of wisdom, I would be very grateful.

Many thanks,

emu85

**shg** · 07-09-2013, 12:06 PM

And when you do that, would you also please post an example workbook?

**emu85** · 07-09-2013, 12:29 PM

Originally Posted by arlu1201

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

I would happily give a clearer title if only I knew how to describe the nature of the problem. Any suggestions will be willingly taken on board. Will try and post some screen shots now to try and clarify things a little.

Thanks,

Emu

**emu85** · 07-09-2013, 12:52 PM

Ok, I've erased names for data protection and have translated a few key column titles from Welsh into English to hopefully make it clearer what I am trying to do. Any 'results' are purely fictional at the mo, I was simply checking my formulas had worked.

My 'new trick' today was discovering that the $ will stop my arrays from going walkabout. #geeky pleasures :D

We have two different predictions, FFT(column d) and TP (column e). These grades may or may not be the same. Taking pupil 1 as an example, I want Excel to compare his grade for task one (i3) against his FFT and TP. I want cells k3 and m3 to show whether the result in i3 is higher, equal to or lower than the respective predicted grades.

I would like cells in columns j and l to work out the difference between the total score for portfolio thus far (z column keeps a running total) and the minimum score required to achieve the target grade for that pupil (grade boundaries are in lookup table in columns ai and al).

Thanks,

Emu

**shg** · 07-09-2013, 01:06 PM

I think you haven't explained everything, as will shortly become evident

, but in J3,

=CHOOSE(SIGN(FIND(I3, "ABCDF") - FIND(D3, "ABCDF"))+2, ">", "=", "<")

Similarly, in L3,

=CHOOSE(SIGN(FIND(I3, "ABCDF") - FIND(D3, "ABCDF"))+2, ">", "=", "<")