- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Complicated Excel Question - Is this possible?
Posted on 8/21/13 at 3:01 pm
Posted on 8/21/13 at 3:01 pm
Consider the following scenario...
The goal is to make Cell C9 get conditionally formatted by relatively comparing the scores for C9 in Sheet A, B, and C.
Comp A, C9 = 3.0
Comp B, C9 = 4.0
Comp C, C9 = 1.0
The end goal is for this to conditionally format on a gradient from green (good) to red (bad)…so 4.0 is dark green, 3.0 is gradient towards yellow, and 1.0 is red.
Comp A, C10 = 2.0
Comp B, C10 = 2.0
Comp C, C10 = 3.0
I would want this to look where 3.0 is dark green because is it much better relative to the two other scores and the 2.0s would be closer to a yellow/orange gradient since they are average scores relative to the mean of 2.33 for the discrete population.
Each cell combination formatting is independent from the next row or column on each sheet, but is dependent on the same cell in the other sheets.
I am turning to the great TDings MTB to for assistance -- I am stumped and don't know if this is possible in this program.
TIA
The goal is to make Cell C9 get conditionally formatted by relatively comparing the scores for C9 in Sheet A, B, and C.
Comp A, C9 = 3.0
Comp B, C9 = 4.0
Comp C, C9 = 1.0
The end goal is for this to conditionally format on a gradient from green (good) to red (bad)…so 4.0 is dark green, 3.0 is gradient towards yellow, and 1.0 is red.
Comp A, C10 = 2.0
Comp B, C10 = 2.0
Comp C, C10 = 3.0
I would want this to look where 3.0 is dark green because is it much better relative to the two other scores and the 2.0s would be closer to a yellow/orange gradient since they are average scores relative to the mean of 2.33 for the discrete population.
Each cell combination formatting is independent from the next row or column on each sheet, but is dependent on the same cell in the other sheets.
I am turning to the great TDings MTB to for assistance -- I am stumped and don't know if this is possible in this program.
TIA
3
Posted on 8/21/13 at 3:05 pm to lynxcat
I hope someone can tell you how to do it.
Several times I have wanted to have the results in a cell be different colors based on testing the value in the cell but I've never been able to do it except for a negative result being red and a positive result being black.
I'll wait with you.....
Several times I have wanted to have the results in a cell be different colors based on testing the value in the cell but I've never been able to do it except for a negative result being red and a positive result being black.
I'll wait with you.....
This post was edited on 8/21/13 at 3:07 pm
Posted on 8/21/13 at 3:08 pm to LSURussian
My attempt was to move all data into a singular sheet, conditionally format and then copy that formatting over to each individual cell.....
........fail. Does not work. Defaults to only recognizing a discrete set of 1 for that cell rather than the population you are copying the formatting from.
........fail. Does not work. Defaults to only recognizing a discrete set of 1 for that cell rather than the population you are copying the formatting from.
Posted on 8/21/13 at 3:16 pm to LSURussian
quote:
Several times I have wanted to have the results in a cell be different colors based on testing the value in the cell but I've never been able to do it except for a negative result being red and a positive result being black.
You can put endless conditional formats on a given cell... couldn't you just manually set the colors to match whatever value is in the cell by adding rules? You could also use the color scale option in the conditional formatting table
This post was edited on 8/21/13 at 3:25 pm
Posted on 8/21/13 at 3:20 pm to hiltacular
Have you tried the conditional formatting? What issues did you run into?
Have you tried mrexcel.com?
Have you tried mrexcel.com?
Posted on 8/21/13 at 3:22 pm to seawolf06
Yeah I don't get what issues you are having... you can set your rules in the conditional formatting to be whatever you want...
I just verified that you can set rules based on other sheets...
You will basically set a rule that applies to cell C9 where the formula calls for cell C9 on the other sheet.
There is even a "color scale" option in the conditional formatting table where you can compare values and get a related color.
I just verified that you can set rules based on other sheets...
You will basically set a rule that applies to cell C9 where the formula calls for cell C9 on the other sheet.
There is even a "color scale" option in the conditional formatting table where you can compare values and get a related color.
This post was edited on 8/21/13 at 3:25 pm
Posted on 8/21/13 at 3:24 pm to lynxcat
Under home go to Conditional Formatting
Click on Highlight Cells Rules
(There are several choices such as Equal to, between, greater than, etc.)
If you can find a rule that applies to what you are trying to do you can have the color you choose fill those cells that satisfy that condition.
Hope this helps.
Click on Highlight Cells Rules
(There are several choices such as Equal to, between, greater than, etc.)
If you can find a rule that applies to what you are trying to do you can have the color you choose fill those cells that satisfy that condition.
Hope this helps.
Posted on 8/21/13 at 3:26 pm to PlanoPrivateer
Posted on 8/21/13 at 3:31 pm to hiltacular
Easy way:
Link the values from each sheet to the other sheets then use the appropriate color scale conditional formatting. Hide the values linked from the other sheets. Done?
In the conditional formatting dialogue, it appears that conditional formatting is sheet specific, so in order to do this another way, you are going to have to pull everything to the reference sheet.
I kind of agree that this seems easy, but you could be over simplifying your issue.
ETA:
If the values are set, you can also use "Format all cells based on their values" set the lowest value to 1 and highest to 4, pick the colors and paste the formats to all of your C9s.
Link the values from each sheet to the other sheets then use the appropriate color scale conditional formatting. Hide the values linked from the other sheets. Done?
In the conditional formatting dialogue, it appears that conditional formatting is sheet specific, so in order to do this another way, you are going to have to pull everything to the reference sheet.
I kind of agree that this seems easy, but you could be over simplifying your issue.
ETA:
If the values are set, you can also use "Format all cells based on their values" set the lowest value to 1 and highest to 4, pick the colors and paste the formats to all of your C9s.
This post was edited on 8/21/13 at 3:36 pm
Posted on 8/21/13 at 4:13 pm to lynxcat
I don't think you guys quite understand the problem. The range of values don't set the color range. The amount by which the values deviate from the mean sets the color range.
You will probably need to make a "means" sheet, and then a deviation sheet for each of the others, and then maybe a max dev sheet.
For example, with this data:
Cell C9 on your "means" sheet would have a value of 2.67. Cell C9 on sheet Dev A would have the value (3.0 - 2.67) = 0.33. Cell C9 on sheets Dev B and Dev C would have the values 1.33 and -1.67, respectively. Your max dev sheet would pluck the absolute max of your deviations, which in this case is -1.67. Now you have a deviation range to base your color range on for this particular set of cells, with -1.67 being max red / min green, and 1.67 being max green / min red.
I don't use spreadsheets much, so I can't say exactly how to do the next step, but I'm sure it's possible. The red and green values are probably on a scale from 0-255, so from here it's just a matter of doing the math to set your red/green values based on how close to the extremes of your range they are. For example, a value that is exactly equal to the mean would have red/green values of 255/255. A negative deviation would reduce the green value, and a positive deviation would reduce the red value, proportionally so that it reaches 0 at your max deviation. This way, Comp C's cell will have the color values of 255/0 (100% red, no green), Comp B's cell will have the color values of 51/255 (100% green with some red to give it a slightly yellow tint), and Comp A's cell will have the color values 204/255 (100% green with a good bit of red to get it much closer to yellow).
Damn I hope all that makes sense.
You will probably need to make a "means" sheet, and then a deviation sheet for each of the others, and then maybe a max dev sheet.
For example, with this data:
quote:
Comp A, C9 = 3.0
Comp B, C9 = 4.0
Comp C, C9 = 1.0
Cell C9 on your "means" sheet would have a value of 2.67. Cell C9 on sheet Dev A would have the value (3.0 - 2.67) = 0.33. Cell C9 on sheets Dev B and Dev C would have the values 1.33 and -1.67, respectively. Your max dev sheet would pluck the absolute max of your deviations, which in this case is -1.67. Now you have a deviation range to base your color range on for this particular set of cells, with -1.67 being max red / min green, and 1.67 being max green / min red.
I don't use spreadsheets much, so I can't say exactly how to do the next step, but I'm sure it's possible. The red and green values are probably on a scale from 0-255, so from here it's just a matter of doing the math to set your red/green values based on how close to the extremes of your range they are. For example, a value that is exactly equal to the mean would have red/green values of 255/255. A negative deviation would reduce the green value, and a positive deviation would reduce the red value, proportionally so that it reaches 0 at your max deviation. This way, Comp C's cell will have the color values of 255/0 (100% red, no green), Comp B's cell will have the color values of 51/255 (100% green with some red to give it a slightly yellow tint), and Comp A's cell will have the color values 204/255 (100% green with a good bit of red to get it much closer to yellow).
Damn I hope all that makes sense.
Posted on 8/21/13 at 4:29 pm to Korkstand
quote:
I don't think you guys quite understand the problem. The range of values don't set the color range. The amount by which the values deviate from the mean sets the color range.
You will probably need to make a "means" sheet, and then a deviation sheet for each of the others, and then maybe a max dev sheet.
For example, with this data:
quote:
Comp A, C9 = 3.0
Comp B, C9 = 4.0
Comp C, C9 = 1.0
Cell C9 on your "means" sheet would have a value of 2.67. Cell C9 on sheet Dev A would have the value (3.0 - 2.67) = 0.33. Cell C9 on sheets Dev B and Dev C would have the values 1.33 and -1.67, respectively. Your max dev sheet would pluck the absolute max of your deviations, which in this case is -1.67. Now you have a deviation range to base your color range on for this particular set of cells, with -1.67 being max red / min green, and 1.67 being max green / min red.
I don't use spreadsheets much, so I can't say exactly how to do the next step, but I'm sure it's possible. The red and green values are probably on a scale from 0-255, so from here it's just a matter of doing the math to set your red/green values based on how close to the extremes of your range they are. For example, a value that is exactly equal to the mean would have red/green values of 255/255. A negative deviation would reduce the green value, and a positive deviation would reduce the red value, proportionally so that it reaches 0 at your max deviation. This way, Comp C's cell will have the color values of 255/0 (100% red, no green), Comp B's cell will have the color values of 51/255 (100% green with some red to give it a slightly yellow tint), and Comp A's cell will have the color values 204/255 (100% green with a good bit of red to get it much closer to yellow).
Damn I hope all that makes sense.
Korkstand -- first of all.
Second of all, I think this works.
Third of all, I think the scale I am trying to do this for is too massive to be able to do that for each individual cell. I have over 80 sheets and around 35 cells where I would need to write this coding. I believe it would work, but I do not have the time to do this at that scale
To everyone else, yes, I am extremely familiar with Excel and conditional formatting. I don't even know how to search for this solution via MrExcel or Chandoo...very specific topic.
I think the answer is just going to have to be "it is possible, but it might take weeks and we don't have that kind of time".
Posted on 8/21/13 at 4:51 pm to lynxcat
I just tried it out on Excel, and I was able to do it, but yeah it seems like it would be time-consuming.
Under conditional formatting->more rules, change the format style to 3 color, set your value types to formula, and input the values as such:
Midpoint is average of cells.
Minimum is average minus absolute max deviation.
Maximum is average plus absolute max deviation.
Forgive me, but I am about to edit with a photo of a screen instead of an actual screenshot
I realize my values are on the same sheet, whereas yours would be spread across multiple sheets, but it should work the same. I just needed to show how the colors compare to one another.
Under conditional formatting->more rules, change the format style to 3 color, set your value types to formula, and input the values as such:
Midpoint is average of cells.
Minimum is average minus absolute max deviation.
Maximum is average plus absolute max deviation.
Forgive me, but I am about to edit with a photo of a screen instead of an actual screenshot
I realize my values are on the same sheet, whereas yours would be spread across multiple sheets, but it should work the same. I just needed to show how the colors compare to one another.
This post was edited on 8/21/13 at 4:58 pm
Posted on 8/21/13 at 5:07 pm to lynxcat
(no message)
This post was edited on 8/21/13 at 5:15 pm
Posted on 8/21/13 at 8:41 pm to Korkstand
quote:
Korkstand
That is clutch and exactly what I need it to do. Very creative approach!
Unfortunately, the scale of making that formula for every single cell combination is obnoxious, but it is doable if absolutely necessary.
Posted on 8/22/13 at 9:46 am to lynxcat
Yeah with 80 sheets you really need to write a macro at that point.
Posted on 8/22/13 at 9:10 pm to lynxcat
Get Tableau
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News