I have an Excel 2007 spreadsheet for a list of products and a bunch of factors to rate each one on, and I'm using Conditional Formatting to set the color of the cells in the individual attribute columns. It looks something like this:

enter image description here

I want to fill in the rating column for each item with a color, based on the color ratings of its individual attributes. Examples of ways to determine this:

  • the color of the category in which the item scored worst
  • the statistical mode of the category colors
  • the average of the category ratings, where each color is assigned a numerical value

How can I implement any or all of the above rules? (I'm really just asking for a quick overview of the relevant Excel feature; I don't need step-by-step instructions for each rule.)

Sample current rules:

enter image description here

  • Are you using Conditional Formatting on the other colored cells, or are they colored manually? – Iszi Oct 16 '12 at 18:41
  • @Iszi, in a perfect world, I'd like a solution that could work regardless of how the attribute columns got their colors. For the purposes of the mockup image, I actually did do it manually, but I wrote in the question that I used Conditional Formatting. – Pops Oct 16 '12 at 19:23
  • @LordTorgamus Ah, missed that. Well then, the solution should be relatively simple: Use a Conditional Formatting rule that combines all the elements of your other Conditional Formatting rules. – Iszi Oct 16 '12 at 20:00
  • @Iszi No problem, happens to me all the time. Setting up a rule was the first thing I thought of, but I didn't see how to make one rule depend on other rules. Could you explain more in an answer? – Pops Oct 16 '12 at 20:10
  • You don't want to make the rule dependent upon the other rules - that defeats the intent of having the rule work regardless of how the other columns got their color. You make the rule a combination of all the other rules. I'd need to see your existing rules to come up with a viable example, if it's possible for your case at all. – Iszi Oct 16 '12 at 22:05

Without the likes of VBA you can’t access the fill colour so would need to get behind that – to the logic that determined that colour. You have only indicated one set of rules (and these do not appear consistent with your manually applied highlighting) but would need to combine all relevant existing rules for your Rating requirements. A conditional formatting rule that as a formula returns TRUE in F1 should be a start, say =OR($B2{arrow}w,$C2{arrow}x,$D2{arrow}y,$E2{arrow}z) for ‘Worst Category’, depending upon whether greater or less than (ie {arrow}) and your break points.

The above with “Worst Colour” formatting would only work for Worst so would require adjustment to cope with a third (and fourth?) colour unless, as may be easier, using Stop If True and further similar formulae.

“Statistical Mode” and “Average” would require different formulae but the principle is the same.

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.