I have a table with students' answers to 20 math problems:
a. B C. D | E ... ... ------------ + ----- + ----- + ----- + ----- + ... no problem 1 | 2 | 3 | 4 | ... ... ------------ + ----- + + ---- + +----- + ----- + ... Correct answer | 3 | 2 | A. 15 | ... ------------ + ----- + ----- + ----- + ----- + ... Student 1 | 3 | 4 | A. 12 | ... student 2 | 2 | 2 | C. 15 | ... student 3 | 3 | 2 | A. 13. ... ...
Now a column is needed which counts 'correct' answers for each student.
I can do this as:
= (IF (D $ 3 = D5; 1; 0)) + (if (E $ 3 = E5; 1; 0)) + (IF (F $ 3 = F5; 1; 0)) + ...... but this is not the best way :)
This is a typical use case for SUMPRODUCT:
ABCDEFG1 not problem 1 2 3 4 2 Correct answer 3 2a copy Student Correct Answer 3 Student 1 3 4 A 12 2 4 Student 2 2 C 15 2 5 Student 3 3 2 A 13 3 In Formula G3: = SUMPRODUCT ($ B $ 2: $ E $ 2 = $ B3: $ E3) If there are more problem numbers, then $ E $ 2 and $ E3 Shall be increased in the column letters.
How it works: SUMPRODUCT takes its internal functions as an array formula if $ B $ 2 = $ B3, $ C $ 2 = $ C3, $ D $ 2 = $ dependent Then $ B $ 2: $ E $ 2 = $ B3: $ E3 Matrix {TRUE, FALSE, TRUE, FALSE} D3, $ E $ 2 = $ E3
LibreOffice Or OpenOffice is TRUE 1 and FALSE is 0. Therefore SUMPRODUCT is the essence of all TRUE.
In Excel, you must first obtain the boolean value in numerical context. So Excel will have formula = SUMPRODUCT (($ B $ 2: $ E $ 2 = $ B3: $ E3) * 1) .
Formula 3 in Formula 3. Fill all the student rows for $ lines before the line # 2 ensures that there is no change in the line of the correct answer in this way.
Greetings
Axel
Comments
Post a Comment