I've got grades A+ through F which I've assigned a numerical value in a table. I would like to sum those grades using the table as the reference. I know that I could use a sumproduct but I'm think there has to be a quicker way using a vlookup or index. I've attached a sample sheet. Any help would be greatly appreciated.Grade Calculation Sample.xlsGrade Calculation Sample.xls
Last edited by kgibson20; 04-26-2012 at 04:21 PM.
Hi Kgibson,
Use the below formula in cell P2 and drag towards right... thanks.
=VLOOKUP(H2,tables!$A:$B,2,0)
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Unfortunately space will be a consideration and I need for the process to take up on cell. I know I can do it with a sumproduct but thats a rather large formula.
Okay.. which cell do you want to have the formula in ?
And with reference (seeing critieria) of which cell ?
Regards,
DILIPandey
<click on below 'star' if this helps>
My solution would be:
Reverse your table so that F is at top and A+ at bottom (easily done by selecting both columns and sorting second column ascending).
Now you can use this array formula:
=SUM(MATCH(H2:O2,tables!$A$3:$A$15,0)) entered with Ctrl+Shift+Enter
and copied down.
It makes use of the positions of the grades - regardless of the points awarded for the grades so if you change the points this would not work.
Last edited by Cutter; 04-26-2012 at 03:41 PM.
Sort your grade table ascending ...
Then in P2 and copy down,
Please Login or Register to view this content.
=INDEX(tables!$A$3:$A$15, MATCH(SUM(SUMIF(tables!$A$3:$A$15, QB!H2:O2, tables!$B$3:$B$15))/COUNTA(QB!H2:O2), tables!$B$3:$B$15))
That formula MUST be confirmed with Ctrl+Shift+Enter.
Brought to you by DLL
Last edited by shg; 04-26-2012 at 03:46 PM.
Entia non sunt multiplicanda sine necessitate
Cutter - I placed the formula directly to the right of the grades and it worked to a degree. It came back 7 higher than it was supposed to be. Any idea why?
If you're talking about my formula - I put it in P2 and it returns 38. I believe that is the correct answer.
It returned a D+. I'm looking to see what needs to be switch around. If you know please let me knowOriginally Posted by shg
Sort your grade table ascending ...
Then in P2 and copy down,Please Login or Register to view this content.
=INDEX(tables!$A$3:$A$15, MATCH(SUM(SUMIF(tables!$A$3:$A$15, QB!H2:O2, tables!$B$3:$B$15))/COUNTA(QB!H2:O2), tables!$B$3:$B$15))
That formula MUST be confirmed with Ctrl+Shift+Enter.
Brought to you by DLL
Were you using my original formula to get the wrong answer? The one I deleted? I replaced it with a corrected one. See Post #5 and note the sorting change to your table.
=SUM(MATCH(H2:O2,tables!$A$3:$A$15,0)) entered with Ctrl+Shift+Enter
I keep re -doing it and I keep coming up with a 76. Does it mater if the table gets sorted?
The average of those grades is 4.75, which is a D+.
I wouldn't have told you to re-sort it if it didn't matter.
Ok I see, I didn't get that. i was thinking still along the lines of a vlookup. Is there no way to set it up to where the values associated with the grades can be changed?
Sorry I didn't see the sort part the first time. Thanks for the help.
Thats not what I was originally going for but I'm going to use that as part of my analysis. Thanks for the formula.
You can take part of shg's formula:
=SUM(SUMIF(tables!$A$3:$A$15, QB!H2:O2, tables!$B$3:$B$15)) also array entered (C+S+E)
and using that part only does not require re-sorting your table - it can be sorted either way
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks