Explanation
At the core, we are using the INDEX function to retrieve the value at a given row or column number like this:
=INDEX(C5:G9,row,column)
The range C5:C9 defines the matrix values. What’s left is to figure out the correct row and column numbers, and for that we use the MATCH function. To get a row number for INDEX (the impact), we use:
MATCH(impact,B5:B9,0)
To get a column number for INDEX (the impact), we use:
MATCH(certainty,C4:G4,0)
In both cases, MATCH is set up to perform an exact match. When certainty is “possible” and impact is “major” MATCH calculates row and column numbers as follows:
=INDEX(C5:G9,4,3)
The INDEX function then returns the value at the fourth row and third column, 12.
For a more detailed explanation of how to use INDEX and MATCH, see this article .
Explanation
This formula uses the named range key (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(--(C7:G7=key))
working from the inside-out, this expression is evaluated first:
C7:G7=key // compare answers to key
The result is an array of TRUE FALSE values like this:
{TRUE,TRUE,TRUE,FALSE,TRUE}
TRUE values indicate a correct answer, FALSE values indicate an incorrect answer.
To coerce the TRUE and FALSE values to numbers, we use a double negative :
--({TRUE,TRUE,TRUE,FALSE,TRUE}) // get 1's and 0's
The is an array of 1’s and 0’s delivered directly to the SUM function:
=SUM({1,1,1,0,1}) // sum correct
The SUM function then returns the final result, 4.
Incorrect answers
The formula in J7 counts incorrect answers in almost the same way:
=SUM(--(C7:G7<>key))
The only difference is that we are now using the not equal to (<>) logical operator:
=SUM(--(C7:G7<>key))
=SUM(--({FALSE,FALSE,FALSE,TRUE,FALSE}))
=SUM({0,0,0,1,0})
=1
Percent correct
The formula in K7 calculates the percentage of correct answers like this:
=I7/COUNTA(key) // divide correct by total
Cell I7 already contains the count of correct answers. This is divided by the total count of quiz answers, which is calculated with the COUNTA function:
COUNTA(key) // count total
The result is formatted with the percentage number format .
