help in excel RRS feed

  • Question

  • total marks
    q1 q2 q3
    Student  2 3 4
    a 1 2
    b 1 2
    c 1 2
    d 1
    e 1 2
    f 2 1 1

    Consider this as an excel sheet students were given a test in which 3 questions each has max marks given as 2,3,4. Below is the marks secured by students. blanks are the questions not attempted by students. Now i want to calculate query such that total marks scured/total marks attempted.

    viz., student a didnt attempted q1 but answered q2 and q3 and got total of 3 out of 9 marks. What i need is since he didnt attempted q1 i want only 3 out of 7. 

    can any body help how to write logic regarding this query.

    • Edited by sasi4uall Thursday, August 10, 2017 5:48 AM
    Thursday, August 10, 2017 5:47 AM

All replies

  • Use an array formula.  If the max marks are in D4-F4 and the student's marks are in D9-F9, then

         The student's total is =SUM(D9:F9)

         The max possible value is =SUM(IF(ISBLANK(D9:F9),0,D4-F4))

    Remember that array formulas require Ctrl-Shift-Enter.  If the entire formula is not enclosed in braces ({ and }), then it was not entered properly.

    Thursday, August 10, 2017 7:03 AM