This Excel file is more of a custom solution than it is a template.

So, why share it?

Because, you can learn a lot about Excel by auditing formulas!

Take apart the template and see how it works (password is ‘test’).

Use Excel’s formula auditing feature (found under ‘FORMULAS’ on the ribbon) to audit the mega array formula that is beneath ‘Grade%’ in sheet ‘Class#1’.

 

Download my Excel file

Download here or via my OneDrive (file 00061)

 

Watch my YouTube video

 

Here is the Formula for ‘Grade%’

IF($B17=””,””,

IF($D$1=0,””,

IF(AND(COUNTIFS(Class2[@[Input1]:[Input20]],0,$E$11:$X$11,TRUE)>0,SUM(Class2[@[Input1]:[Input20]])=0   ),0,

IF(COUNT(Class2[@[Input1]:[Input20]])=0,””,

IF( SUM(Class2[@[Input1]:[Input20]]*$E$11:$X$11) =0,””,

IFERROR(

SUM(IF($E$11:$X$11=TRUE,IF(Class2[@[Input1]:[Input20]]<>””,Class2[@[Input1]:[Input20]]/$E$7:$X$7*$E$9:$X$9,””)))

/SUM(IF($E$11:$X$11=TRUE,IF(ISNUMBER(Class2[@[Input1]:[Input20]]),$E$9:$X$9,””))),

0))))))

 

Why such a Complicated Formula?

Well…the requirements were:

1) Adjustable ‘Pass Score’

2) Ability to change the ‘Weight’ for all assignments (‘Grade%’ updates)

3) Ability to quickly exclude an assignment (or re-include it).

4) See stats per assignment (‘Average’ score and ‘Pass’…”7 out of 11″ Passed)

Can you think of a shorter formula that will still calculate as required?

 

What else does the ‘Class Student Grade Calculator’ do?

1) One click hides student names (check box beside ‘Student_Name’)

2) Ability to show failed assignments in red (or turn this feature off)

3) Show class average  (or turn this feature off)

4) Probably something else that I’ve now forgotten!

 

Subscribe to my YouTube channel and learn more!

See my free templates here!

4 Comments

  1. Kerry Gwen says:

    I was in search of template to calculate grades of my students in painless way. I searched and found (https://www.opptrends.com/document-management-best-practices-for-small-businesses/) good one. But your video tutorial is a great help. Simply mind blowing. Good Work dude.

    1. Kevin Lehrbass says:

      Hi Kerry,
      I’m glad you enjoyed my video.
      Thanks for visiting my blog.
      Cheers,
      Kevin

  2. Yulaidy Pavon says:

    You’re a live saver!

    1. Kevin Lehrbass says:

      Hi Yulaidy,
      I’m glad you found it helpful. Thanks for visiting my blog.
      Kevin

Post a comment