conditional formatting

[kebula]

Hello, I wonder if there is a formula to mark the highest score on different colors, depending if it's positive or negative.
I know it sounds quite complicated so I'll show you the example.

Here, we have a small table with a survey result
Capture excel.PNG

I want to mark the highest score with green if it's in "strongly agrees" and "agree", yellow if it's "neutral" and red if it's "disagree" and "strongly disagree". is it possible?

[Special-K]

1. if it's in "strongly agrees" and "agree"

You mean the highest score is BOTH strongly agrees AND agree? Therefore the highest value appears at least twice in the column?
Or do you mean "strongly agrees OR agree" not AND ?

2. Same question wth
if it's "disagree" and "strongly disagree".

Do you mean AND or do you mean OR?

3. Why are there no negative values in the example data that you speak of?

[Richard Buttrey]

As always with these things it much easier to give an answer if we can see the workbook where the results have been manually added.

Therefore please upload your workbook and manually paint the cells with the appropriate colours.

[kebula]

1,2. good point! OR would work better
3. there are no negative value. It's the result of a survey and it counts the number of pepole who checked a given opinion. Using "negative", I meant a negative opinion

[Glenn Kennedy]

Maybe like this:

=AND(ISNUMBER(SEARCH(" disagree"," "&$A2)),B2=MAX(IF(ISNUMBER(SEARCH(" disagree"," "&$A$2:$A$6)),$B$2:$C$6)))

similarly for agree... remember the space before disagree is important!! Don't leave it out.

[kebula]

Ekhm, it looks i'm not so good at Excel I thought. I can't share the whole workbook and the data are linked from one sheet to another. I all disappears when I try to copy separately.

[kebula]

wonderful Glenn!
What rule did u use?

[Glenn Kennedy]

The rule is shown in Post 5.... leaving out the d in disagree in the other rule....

??? Or are you asking a different question??

if not, You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.