Bar Chart Changing Colors depending of a Range? possible? 2

[RovirozM]

Hi Guys,

I'm new working with Charts with Microsoft Excel (In my case Office 2003 yet).

I finished my work but the only thing that I still missing is that I don't know how to handle that every bar change the colors depending of one range... for example:

Day1 Day2 Day3
Worker 1 5 7 4
Worker 2 10 8 7
Worker 3 9 5 8

Imagine that this creates a Bar Chart and I would like to manage an average that if the Worker/Day is 5 or less will be a Red Bar, if not will be a Green Bar....

It is possible to do this? if yes can you tell me how?

Thanks!

 

[SkipVought]

Hi,

Yes. This can be accomplished using IF formulas on the sheet, in a new column, like...
[tt]
E2: =IF(Average(B22)<5,Average(B2:B4),0)
[/tt]
This column's series would be red.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RovirozM]

Hi Skip,

Thanks for the information, but I ignore how to do this... What I understand is that if it is less than 5 it is going to put another average if not a cero... but how my color graphs bars will change with this one? can you please give me more details?

Thanks again!

 

[SkipVought]

You have a column of data that corresponds to a SERIES in your chart. Let's say it is column E. You shade that series RED. Let's say that column F has the complementary formula that displays the value greater than ZERO, when column E is ZERO or is ZERO when column E has a value greater than ZERO. That corresponding series is shaded GREEN.

Whenever the value is ZERO, then that series ZERO-length bar, although it has a color, you never get to see it. The other series, for that corresponding point, does have a value greater than ZERO, so you CAN see it and its color.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RovirozM]

Hi SkipVought

Sorry but I tried to understand it and I couldn't follow you also I ask a friend but We couldn't try what do you tell us... do you know any web page about the topic that can help me to change my Bar Chart depending of the average of my table?

Thanks!

MR

 

[SkipVought]

Please post a sample of your data that you are currently using as source for your chart.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RovirozM]

Thanks Skip,

I made a very easy Chart in this Excel File:

http://www.mediafire.com/?ikjdznyizemytz4

Here is just two columns:

A B
4 6
3 3
6 4

When the column is >5 I would like my Bar on my chart in Green, if it is <5 I would like my bar in Red...

Right now the series of my bars are blue, red and yellow.

I appreciate your help.

 

[SkipVought]

[tt]
RED GREEN RED GREEN
A B C D E F
4 6 =if($A1<5,5,0) =if($A1>=5,5,0) =if($B1<5,5,0) =if($B1>=5,5,0)
3 3
6 4
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RovirozM]

Hi,

I really appreciate your help, I understand that this will put a 5 or cero on the columns, but how is linked to the bar char to change his color? I ignore how it works...

Thanks!

 

[SkipVought]

CHANGE a value in column A or B!!!!!

Plot columns C:F

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RovirozM]

Nothing happen ;O( just the Bar is lower or higher but with the same color... did you see my file?

Thanks!

 

[SkipVought]

so sorry ...
[tt]
4 6 =if($A1<5,$A1,0) =if($A1>=5,$A1,0) =if($B1<5,$B1,0) =if($B1>=5,$B1,0)
3 3
6 4
[/tt]
of course, you must copy the 4 formulas down thru all rows fo data

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RovirozM]

Bad News, believe me it is not changing the colors of the Bars on my chart..

Try to see this file and maybe you can tell me what is wrong?

http://www.mediafire.com/?cjzygwdzi3enoin

Thanks a lot!

 

[SkipVought]

1. The color of the CELLS in your sheet are irrelevent!

2. You have 3 series. Any series can only have ONE COLOR. You must SWITCH the orientation from ROW to COLUMN in order to get 4 series: 2 series for A and 2 series for B.

I'd suggest using a STACKED COLUMN rather than CLUSTERED COLUMN. It would work MUCH better!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RovirozM]

Not working ;O(

Do you know any webpage talking about this? that can help me to change this bar colors?

 

[SkipVought]

see if you understand this.

faq68-7367

Work thru a similar example and make it work, and then apply to your need.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]