# Help: SUMPRODUCT/LOOKUP for values

1. ## 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).

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

2. ## Re: Help: SUMPRODUCT/LOOKUP for values

I'm not sure I understand what you're attempting, but this line in particular throws me off:

Originally Posted by Jones90
Cell K5 should show +1, as they are 3 grades above target, with cell L5 having an E for 'exceeded'
If the target is 5A and the Summer mark is 5A, shouldn't K5 show 0?

My best guess at what you're doing is that you want column K to calculate the difference between the target in J and the most recent mark for any of the periods in E:H. If I've got that right, you should be able to use the formula below in K3. It should be array-entered (confirm with Ctrl + Shift + Enter instead of Enter):

Fill it down for the rest of column K. Give that a try and see if it proves helpful. If not, could you please clarify what you're looking for - perhaps with a few more explanations of expected values?

Similarly, for I3 you can array-enter the following and fill down:

3. ## Re: Help: SUMPRODUCT/LOOKUP for values

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?

4. ## Re: Help: SUMPRODUCT/LOOKUP for values

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

5. ## Re: Help: SUMPRODUCT/LOOKUP for values

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?

J

6. ## Re: Help: SUMPRODUCT/LOOKUP for values

Maybe something like this:

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

7. ## Re: Help: SUMPRODUCT/LOOKUP for values

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

8. ## Re: Help: SUMPRODUCT/LOOKUP for values

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.

9. ## Re: Help: SUMPRODUCT/LOOKUP for values

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

10. ## Re: Help: SUMPRODUCT/LOOKUP for values

I'll have another look. It's not complex, just not very clearly labelled - you should see some of my departmental and whole school workbooks!!! Watch this space ...

11. ## Re: Help: SUMPRODUCT/LOOKUP for values

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

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

12. ## Re: Help: SUMPRODUCT/LOOKUP for values

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

13. ## Re: Help: SUMPRODUCT/LOOKUP for values

Glad to have helped. I fail to see why a minus score would indicate exceeding one’s target and a positive one not meeting it, but hey, if it works, it works!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1