If statement issue

[hirobrad_1]

Good evening all, Merry Christmas and such.

I am having a huge issue and I can not seem to think how to fix it. I am a teacher and things have changed in the UK. I am attempting to work out how many people in my classes have made positive progress in the manner the government and Ofsted want it presented.

We use letter grades, however, it is not possible to track progress on this. For example:

Target	Current	Progress Score
B	A	1
C	E	-2
F	C	+3
D	D	0
A*	B	-2

<tbody>
</tbody>

Then you add all the progress scores together this class would have a progress of 0. Some students did poorly however others did well.

it is simple with one cell when I know the Target. For example:

=if(a1=B then 0, or if C then -1 or if D then -2)

but each target and therefore the progress score is different. Am I being really stupid? I can't seem to solve this.

Any assistance would be huge!

Thanks for looking

 

[Eric W]

Re: If statement issue (I think, it could be something I am unaware of)

Welcome to the forum.

You could try something like:

	A	B	C
1	Target	Current	Progress Score
2	B	A	1
3	C	E	-2
4	F	C	3
5	D	D	0
6	A*	B	-2
7
8			0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula C2 =MATCH(A2,{"A*","A","B","C","D","E","F"},0)-MATCH(B2,{"A*","A","B","C","D","E","F"},0) C8 =SUM(C2:C6) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[njimack]

Re: If statement issue (I think, it could be something I am unaware of)

The below almost works, but it doesn't cater for A* - maybe another poster will be able to improve on it...
=MATCH(B2,{"F","E","D","C","B","A"},0)-MATCH(A2,{"F","E","D","C","B","A"},0)

 

[hirobrad_1]

Re: If statement issue (I think, it could be something I am unaware of)

Gentlemen, you are my Christmas heroes!! I can not thank you both enough! I have one other issue but I am not sure of the theory behind it. If a student was to get a U grade that does not have the same logic I believe they would lose more, however I intend none of my students to get a U so it would not apply (I hope)

Have a top Christmas and thank you again guys!

 

[AliGW]

Re: If statement issue (I think, it could be something I am unaware of)

The U grade should have the same logic - what makes you think otherwise? So, if G is the lowest possible grade and is a pupil's target, and that pupil's current performance level is U, then that is -1 - he is under-performing.

In terms of points, as you should know, I think, A*=8, A=7, B=6, C=5, D=4, E=3, F=2, G=1 and U=0 on the QCA scale. But all of this will be moot by the end of this academic year, because of the new 9-1 grading system at GCSE. Unless, that is, you are not doing standard GCSEs?

 

[Eric W]

Re: If statement issue (I think, it could be something I am unaware of)

The MATCH essentially just finds the location of the value in the list. If you were to add "U" to the list after the "F", then "U" to "E" would be +2, and "E" to "U" would be -2. If you were to add all the letters of the alphabet up to U, then "E" to "U" would be -16, which I'd guess you wouldn't want! Stick with your plan of not giving any Us.

Glad we could help.

 

[hirobrad_1]

Re: If statement issue (I think, it could be something I am unaware of)

Good evening,

That is the case for the school results (Attainment 8 score) it takes the students best 8 subjects that are relevant and then, you are correct applies that point system.

I am creating a system that highlights potential under-achievement at a department level so would need the current score and the target to best show who to target for intervention.

 

[AliGW]

Re: If statement issue (I think, it could be something I am unaware of)

I'm head of a large department at my school, so I know all about tracking. It's one of the main reasons I use Excel.