# help in excel

• ### 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 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