Hi - Hope you can all help.
I've learnt a lot about excel recently but have finally been stumped.!!!!
I have produced a sheet that will calculate a total score on a school test, then assign it the appropriate school level using a lookup table (levels are 3C, 4A etc)
This then appears in the end column.
What I now want to do is show progress or regression made eg: +3 levels or -2 levels so I assigned each level a number e.g. 1C (the lowest) is 1 and 5A, the highest is 12, and made a lookup table. I then asked the cells to convert the previous level to a number e.g 4C which became 7 and the new score which was 4A (9) then I used the following formula to get it to take one away from the other:
=LOOKUP(I2,M$22:N$33)-LOOKUP(E2,M$22:N$33)
The new grade is in I2 and the old one in E2 so this should give an outcome of +2 levels progress.
However I'm getting allsorts of strange things - have checked the lookup tables and the references but it seems to give unusual calculations. - Can anyone help or have I not given enough info for a response.
Many thanks for any help you may be able to offer.
SG
I've learnt a lot about excel recently but have finally been stumped.!!!!
I have produced a sheet that will calculate a total score on a school test, then assign it the appropriate school level using a lookup table (levels are 3C, 4A etc)
This then appears in the end column.
What I now want to do is show progress or regression made eg: +3 levels or -2 levels so I assigned each level a number e.g. 1C (the lowest) is 1 and 5A, the highest is 12, and made a lookup table. I then asked the cells to convert the previous level to a number e.g 4C which became 7 and the new score which was 4A (9) then I used the following formula to get it to take one away from the other:
=LOOKUP(I2,M$22:N$33)-LOOKUP(E2,M$22:N$33)
The new grade is in I2 and the old one in E2 so this should give an outcome of +2 levels progress.
However I'm getting allsorts of strange things - have checked the lookup tables and the references but it seems to give unusual calculations. - Can anyone help or have I not given enough info for a response.
Many thanks for any help you may be able to offer.
SG
Last edited: