Set an Excel cell's color based on multiple other cells' colors
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:
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.)
EDIT:
Sample current rules:
microsoft-excel microsoft-excel-2007 conditional-formatting
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
|
show 1 more comment
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:
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.)
EDIT:
Sample current rules:
microsoft-excel microsoft-excel-2007 conditional-formatting
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
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
|
show 1 more comment
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:
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.)
EDIT:
Sample current rules:
microsoft-excel microsoft-excel-2007 conditional-formatting
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
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:
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.)
EDIT:
Sample current rules:
microsoft-excel microsoft-excel-2007 conditional-formatting
microsoft-excel microsoft-excel-2007 conditional-formatting
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
edited Oct 16 '12 at 22:18
Pops
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
asked Oct 16 '12 at 16:37
PopsPops
4,733246390
4,733246390
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
|
show 1 more comment
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
Are you using Conditional Formatting on the other colored cells, or are they colored manually?
– Iszi
Oct 16 '12 at 18:41
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
@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
@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
@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
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
|
show 1 more comment
1 Answer
1
active
oldest
votes
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.
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f488554%2fset-an-excel-cells-color-based-on-multiple-other-cells-colors%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
add a comment |
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.
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
add a comment |
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.
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
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.
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
answered Oct 17 '12 at 12:17
pnutspnuts
5,55032239
5,55032239
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f488554%2fset-an-excel-cells-color-based-on-multiple-other-cells-colors%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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