Formula's used with numbers and letters

Hi,

I am a school teacher trying to adjust my tracking sheet to calculate pupils levels. My excel knowledge is very basic and could use some guidance. I am looking for 2 potential formulas that will do the following.

1 - In cell AE I would like to generate a formula that will take the data entered in cells J:5, L:5, N:5, P:5, R:5, T:5, V:5, X:5, Z:5, AB:5 and AD:5 and give an average level.

2 - In cell AH is it possible to generate a formula that will calculate how many levels of progress the pupils are making - In other words I need Cell I to be calculated against cell J to see how much progress the pupils are making - for example if in cell I:5, a pupil is was given a 3a, and then in cell J:5 is given a 4b, they will have made 2 sub levels of progress. As well as this, can that progress then be averaged out across cells I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to give an overall number of of levels of progress? An then..... can I colour co-ordinate the cell so that if the pupils are making 3 or more sub levels of progress it turns green, 2 sub levels orange and 1 sub level red?

Levels work like this

3c
3b
3a
4c
4b
4a
5c
5b
5a and so on



Any help would be greatly appreciated KS3%20-%20Tracking%20%28new%20progress%29.xls

Hi Jeff,

Thanks for the reply, yes sorry, the Column is AE and the sample is for AE5. I can see what you mean with regards to the average. So I will try and give an example of how I potentially think it could work. The levels are identified below. So for example:

If I can demonstrate in a numerical way then you might be able to make sense of what I am after (although I need it to average the number with the letter). Say a pupil is currently working at a 2a and 3a, I could convert this to a 2.9 and a 3.9 and the average would be 3.4 (3c). Another example, if a pupil is working at 3c, 3a, 4a, 5a then the average would be 4.5 (4b) rounded up or down to the nearest number/letter in the case.

2c - 2.3
2b - 2.6
2a - 2.9
3c - 3.3
3b - 3.6
3a - 3.9
4c - 4.3
4b - 4.6
4a - 4.9 and so on
5c
5b
5a
6c
6b
6a
7c
7b
7a
8c
8b
8a

As for calculating the progress in column AH cell AH5, if a pupil was working say for example at a 2a in cell I5 and then gets a level 3b in cell J5 he/she has moved from a 2a, 3c, to a 3b and has made 2 sub levels of progress. But, if he or she moves from a 2a to a 3a has made 1 level progress. Therefore, on column AH cell AH5 can I some how subtract the level in J5 against I5 to calculate the progress made? And then average it out across I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to state how much progress they have made. For example if in cell I5:J5 they make 2 sub levels of progress then another 2 and another 2 the average will be 2 in cell AH5. I hope this makes it a little clearer.

I have managed to sort the formula out with regards to finding the average from the row. The only formula I need to complete the sheet is for the following. In cell AB:5 I want to work out how many levels of progress each pupil is making, 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?

1001.xlsb