Conditional formatting to highlight Quiz Answers

Question

I checked multiple similar threads but could not find exactly what I need (as far as I can tell).

We prepare quiz shows using google spreadsheets, and we do have the occasional human error. This is what it looks like:

As you can see, we have the correct answer spelled out in letters, but we need to have the number as well. I would like to add conditional formatting in column K so that if the answer matching the number (1 for Answer A, 2 for Answer B, 3 for Answer C) does not match the actual text in column F, it will be highlighted in red. If it matches, it will be highlighted in green (as it is right now, but that's not a conditional formatting).

Would anyone be able to help? We figured out the formula to add a column which will do this check and return "OK" or "WRONG", but I would much rather have it as a conditional formatting.

The formula is: =if(K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10))),"ok","WRONG")

Answer 1

I forgot to update this yesterday but here is the final answer (thanks Irin Thirdwater!):

=(if(K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10))),"ok","WRONG"))="ok"

=(if(K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10))),"ok","WRONG"))="WRONG"

The first line with green highlight, second with red.

Hope this helps in case anyone else needs it!

Answer 2

Conditional formatting formula should return TRUE or FALSE. If =if(K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10))),"ok","WRONG") works fine o a spreadsheet cell, on the conditional formatting formula field instead use

=K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10)))

Answer 3

1. From a formula, how can we perform a conditional formatting based on the output?

Theoretically, you can always treat the formula as a black box and simply wrap it around some checks to isolate the cases you want to format.
For instance, given a formula f returning either OK or WRONG, you can add 2 conditional formattings like:

• = f="OK" --> Highlight in green
• = f="WRONG" --> Highlight in red

Where f is simply the whole formula you got.

Note, however, that this will not work in all cases. And in practice, it's better to just break down your formula and work from there. There's probably already a layer of conditionals to decide whether to return OK or WRONG as well. I.e. the formula f may look like:

=if ( [answerChecking], "OK", "WRONG" )

In this case, your (positive) conditional formatting can simply use the answerChecking portion while the negative counterpart can be achieved by simply negating it (e.g. wrapping it inside a not()).

---

2. Take a step back and see if we can eliminate some middle-man.

Our original problem was that there's some human error when inputting the number based on the correct answer and this question is looking to check those inputs. But we can also just eliminate that human input entirely since we can use a formula to give us the number because it is an aggregation of the actual answer and the choices provided. Indeed, you most likely already have this formula as part of f.

Once you shift the answer number from manual input to formula, adding more choices and shuffling them around will also incur little to no cost (not counting the human work of actually adding in those choices) since you can completely rely on the formula to give you the correct number.