- 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: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 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.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News