Highlighting Multiple Rows Conditional Formatting

[Darko_Giac]

Hi all,

I'm working with a fairly large data set (a fictitious sample of which I have attached), and what I would like to do is try and implement a conditional formatting rule that will highlight cells (in yellow perhaps) that are within 2% or greater than the cells highlighted in green.

The attached workbook is a response frequency table that tracks the number of respondents that chose each option. Overall, I'm finding that the frequency of most respondents is the highest on the correct option.

However, in some cases, almost as many or more students will choose an incorrect response over the correct one. These are the cells I would like to flag. I'm defining "almost as many" as a 2% difference below the frequency of responses for the correct option.

For instance, if there are 5 possible options and Option 1 is the correct response, I'm seeing that 25% of students are choosing Option 1, but 30% are choosing Option 2 and 23% are choosing Option 3, I would like to highlight the 30% and 23% cells.

Hopefully the attached worksheet lay out the exercise more clearly.

Any suggestions would be appreciated.

Thanks!

D.

[protonLeah]

You need a key to specify the correct answer. Since you said:
...there are 5 possible options and Option 1 is the correct response, I'm seeing that 25% of students are choosing Option 1, but 30% are choosing Option 2 and 23% are choosing Option 3, I would like to highlight the 30% and 23% cells. ... So we cann't assume the the max is the correct response.

---

yellow: AND(COLUMN(A3)<>MATCH($F3,$A$2:$E$2,0),A3>=INDEX($A3:$E3,MATCH($F3,$A$2:$E$2,0)))
green: COLUMN(A3)=MATCH($F3,$A$2:$E$2,0)

[Darko_Giac]

You need a key to specify the correct answer. Since you said:
...there are 5 possible options and Option 1 is the correct response, I'm seeing that 25% of students are choosing Option 1, but 30% are choosing Option 2 and 23% are choosing Option 3, I would like to highlight the 30% and 23% cells. ... So we cann't assume the the max is the correct response.

---

yellow: AND(COLUMN(A3)<>MATCH($F3,$A$2:$E$2,0),A3>=INDEX($A3:$E3,MATCH($F3,$A$2:$E$2,0)))
green: COLUMN(A3)=MATCH($F3,$A$2:$E$2,0)

Thanks so much for this protonLeah!

I only have one quick follow-up....I know it might be a tall-ish order, but is there a way to also highlight those values that are within 2% below the correct response? Although it shows that fewer respondents chose the incorrect option, the frequency is close enough to flag.

I've attached a workbook of just one case as an example.

Thank you again!

D.

[protonLeah]

maybe:

Please Login or Register  to view this content.

[Bo_Ry]

Column F, I use
F3 =MATCH(MAX(A3:E3),A3:E3,)
to find # column of Max %
This should give you most of the answers but row 28,46 answer is not max so you need to key in.

Please see attached for CF formula.

[Darko_Giac]

maybe:

Please Login or Register  to view this content.

Thanks protonLeah! Worked like a charm. Much appreciated.

D.

[Darko_Giac]

Column F, I use
F3 =MATCH(MAX(A3:E3),A3:E3,)
to find # column of Max %
This should give you most of the answers but row 28,46 answer is not max so you need to key in.

Please see attached for CF formula.

Thank Bo_Ry, this worked as well!