Help: SUMPRODUCT/LOOKUP for values

Hello,

I am hoping for some help for my spreadsheet.
I am trying to show pupil progress, using 'sub levels'. I have used a VLOOKUP table for tables with 4a higher than 4b, followed by 4c. Levels go all the way from level 1 to level 6 (6 being highest).

I want to measure pupil progress, and how many 'sub levels' they are away from their table. However, this needs to be done at different points in the year, meaning if cells are blank I want it to track progress up to that point (e.g. if in AUT Term, only count sub levels to there). If the cells are full, I want it to show for the final level (being SUM Term).

I have attached my spreadsheet.
For example:
Cell K3 should show 0, as they have achieved target of 4A, also cell L3 should show Y for meeting target
Cell K4 should show 3, but if G4 is updated, this should change I4 and L4 by SUM, also I4 should currently show -1
Cell K5 should show +1, as they are 3 grades above target, with cell L5 having an E for 'exceeded'

I would like to have formulas in the cell if possible, and not use any 'helper' cells

I hope this is somewhat clear and I really appreciate your help
J

formula in column L can not find a value from col A in col H from row 831
Why dates in H earlier than in A?

Thank you, this seems to have solved both columns I & K very well.
It is much appreciated

Originally Posted by tim201110

formula in column L can not find a value from col A in col H from row 831
Why dates in H earlier than in A?

Thank you for your reply. Apologies, but I have yet to type in a formula here as I was unsure of what it would be.

I am trying to work out in L whether they have not met target= show 'N', met target = show 'Y', exceeded target = show 'E'.
However, pupils may have met target by the end of AUT term if they perform very well, therefore, is there a way of searching AUT, SPR & SUM to see whether they have achieved targets, rather than SUM alone?

If not, I believe this could be done simply using an IF < = or > function, using SUM only (Col H) as a ref?

Again, your feedback has been very helpful and always appreciated.
J

Originally Posted by AliGW

Maybe something like this:

=LOOKUP(MAX(E3:K3),{-30,0,1},{"N","Y","E"})

Hello again,

Many thanks for your efforts here, however, I did not seem to work....It showed E in L3, even though it should have shown Y as the targets and final outcome matched....any other suggestions would be most appreciated as always.
J

Originally Posted by AliGW

It would help if I understood your table, but I fear I don't. It's unclear to me what is being measured against what. I should say it's not the levels that are unclear - I am a teacher myself.

Hello again,
Column J is pupils' targets. If pupils have shown that they have achieved their targets in either the AUT (Col F), SPR (Col G), SUM (Col H), this means column L would show 'Y'. If they have not met their target it should show 'N', if they have exceeded their target, e.g. they have scored a 5c, when their target was a 4a then it should show 'E' as they Exceeded their target.

I hope this is clearer. I understand this is rather complex, but as you can see I need all the help I can get.

Many thanks
J

Originally Posted by AliGW

In L3 copied down, assuming that K3 reflects their best result of the three:

=LOOKUP(K3,{-30,0,1},{"N","Y","E"})

Hello,
This seemed to give me opposite results so I changed the E and N around and it works perfectly
Thank you very much for responding, it has really saved me an awful lot of work
J