Grade book with varied weightings; letters to numbers to letters

How do you want the units weighting when one is missing?

Why are you not counting non-graded in their averages, by the way? What is the difference? If they sat the exam, then N should be 0, surely?

With vlookup to determine the weight.

After that a pivot table.

See the attached file.

Re: Grade book with varied weightings; letters to numbers to letters
How do you want the units weighting when one is missing?

Why are you not counting non-graded in their averages, by the way? What is the difference? If they sat the exam, then N should be 0, surely?
Ali

AliGW I am not counting the N as the student may have been absent for that component or it has not been completed or attempted and we are still required to put a grade in.
In terms of the weighting I want it so that it would ignore the blank.
So say it was out A+, blank and C+. This would be an A-. So the calculation for the A+ would become 15/100*40 and the C+ would be 9/100*20. Then to calculate the final grade it would be the sum of the two values divide 60 multiplied by 100. The following equation would be for numerical values only: =ROUNDUP(SUM(B3:C3/60*100),0). I need something that ignores blanks and "N" in the calculation.

At the moment I have modified it to this, but it does not account for the "N" result: =IFERROR(VLOOKUP(ROUND(IFERROR((IF(ISTEXT(H4),(VLOOKUP(H4,'Grade determination'!$B$3:$C$18,2,FALSE)/15*45),0)+IF(ISTEXT(I4),(VLOOKUP(I4,'Grade determination'!$B$3:$C$18,2,FALSE)/15*55),0))/100*15,0),0),CHOOSE({1,2},'Grade determination'!$C$3:$C$18,'Grade determination'!$B$3:$B$18,),2,0),)

@mcgudo

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @Oeldere).

It is not clear to my to who you are replying #2 or #3.

I suppose to #2.

Give this a go in K4:

=INDEX('Grade determination'!$B$3:$B$17,MATCH(ROUND(SUMPRODUCT(((SUBSTITUTE(H4:J4,"N","")<>""))*({0.4,0.4,0.2})*('Grade determination'!$B$3:$B$17=H4:J4)*('Grade determination'!$C$3:$C$17))*(1/SUMPRODUCT((H4:J4<>"")*(H4:J4<>"N")*({0.4,0.4,0.2}))),0),'Grade determination'!$C$3:$C$17,0))

Let me know if you have any questions.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Hi AliGW, this does work thank you. I modified it slightly as it was giving me a DIV0 error when all cells are empty.
=IFERROR(INDEX('Grade determination'!$B$3:$B$17,MATCH(ROUND(SUMPRODUCT(((SUBSTITUTE(H4:J4,"N","")<>""))*({0.4,0.4,0.2})*('Grade determination'!$B$3:$B$17=H4:J4)*('Grade determination'!$C$3:$C$17))*(1/SUMPRODUCT((H4:J4<>"")*(H4:J4<>"N")*({0.4,0.4,0.2}))),0),'Grade determination'!$C$3:$C$17,0)),"")

Hopefully that fixes everything. It seems to at the moment. Thanks again

It worked with empty cells for me, as can be seen in the workbook I attached - if you mean when ALL cells were empty, then yes, the error trap is what you needed to add.

It was fun to work out.

Here's the advice I give about responding in threads:

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

Here's a version using LET to set parameters/criteria:

=LET(u,H4:J4,uxn,SUBSTITUTE(u,"N","")<>"",w,{0.4,0.4,0.2},gdb,'Grade determination'!$B$3:$B$17,gdc,'Grade determination'!$C$3:$C$17,IFERROR(INDEX(gdb,MATCH(ROUND(SUMPRODUCT(((uxn))*(w)*(gdb=u)*(gdc))*(1/SUMPRODUCT(((uxn))*(w))),0),gdc,0)),""))