Formula solution to do the following:
In cell AB:5 I want to work out how many levels of progress each pupil is making ( I have shown the levels below), to work this out I need to take the value from cell Q5 away from the value in cell I5 to return a numerical value. For example, as the pupil got a 3a in cell I5 and then a 4c in cell Q5 he/she has made 1 sub level of progress, I want this to show as 0.5 and if he/she moved from a 3a to a 4b it would be 1.0 and then from a 3a to a 4a would be 1.5 and so on.
Can I then do the same in cell AB:5 but take the average of the comparison from I5 and Q5, J5 and R5, K5 and S5, L5 and T5, M5 and U5, N5 and V5, O5 and W5, P5 and X5 to show what average levels of progress the pupil is making. Can the formula also work with cells in that row being empty?
Levels work as follows:
2c
2b
2a
3c
3b
3a
4c
4b
4a
up to 8a
So every time a pupil for example moves from a 2c to a 2b this is a sub level and from 2c to 3c would be one whole level. I want each sub level to be represented as a numerical value of 0.5 so one whole level would represent 1.5. Is this possible? Any help would be greatly appreciated as I have been racking my brains for days now
In cell AB:5 I want to work out how many levels of progress each pupil is making ( I have shown the levels below), to work this out I need to take the value from cell Q5 away from the value in cell I5 to return a numerical value. For example, as the pupil got a 3a in cell I5 and then a 4c in cell Q5 he/she has made 1 sub level of progress, I want this to show as 0.5 and if he/she moved from a 3a to a 4b it would be 1.0 and then from a 3a to a 4a would be 1.5 and so on.
Can I then do the same in cell AB:5 but take the average of the comparison from I5 and Q5, J5 and R5, K5 and S5, L5 and T5, M5 and U5, N5 and V5, O5 and W5, P5 and X5 to show what average levels of progress the pupil is making. Can the formula also work with cells in that row being empty?
Levels work as follows:
2c
2b
2a
3c
3b
3a
4c
4b
4a
up to 8a
So every time a pupil for example moves from a 2c to a 2b this is a sub level and from 2c to 3c would be one whole level. I want each sub level to be represented as a numerical value of 0.5 so one whole level would represent 1.5. Is this possible? Any help would be greatly appreciated as I have been racking my brains for days now