1

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:

Example question

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")

  • What is the formula that you already figured out? – Rubén Apr 11 '18 at 16:36
  • =if(K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10))),"ok","WRONG") – Jiend Apr 12 '18 at 12:07
0

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.

  • Thank you for that! :) It worked with the formula we had. – Jiend Apr 13 '18 at 8:54
0

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)))

  • Looks like the one we're now using indeed, yours is probably more optimized but since ours seems to work just fine I'll leave it at that. Thank you so much for the answer! – Jiend Apr 13 '18 at 8:56
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!

  • 1
    You can reduce the first case to =K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10))) (the condition inside the if()) as in Ruben's answer, and the second to the same one but wrapped around a not() like: =not(K3=if(F3=G3,1,if(F3=H3,2,if(F3=I3,3,10)))). In my answer, I first talked about wrapping them simply to demonstrate that it's theoretically possible by treating them as a black-box and isolating the cases you want to format. But in practice, you want to break it down and just use what you need. – Irin Thirdwater Apr 13 '18 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.