Charts - Conditional Formatting

[dstilson]

I am trying to chart a MTD and YTD series of data, but want the fill format for stacked bar chart to change based on a formula. The colors I'm using are Red, Yellow, and Green. I can get the first series to work under a stacked chart, but when adding the second series I'm having difficulty. I want the bars to be side by side instead of on top of each other. Here is an example of data I'm using.

599 MG MY MR YG YY YR
Jan 590 582 590 582 590 #NA #NA 590 #NA #NA
Feb 569 587 579 584 569 579
Mar 586 591 582 587 586 582

As I mentioned above, I can get the chart to work using a stack chart for MG,MY,MR. But when trying to add the YG etc, I'm not able to get them side by side. Any help is appreciated. thanks

 

[SkipVought]

Hi,

Charts do not have conditional formatting theu Excel 2003.

However, you can make it appear to do that using multiple series.

Let's say I have a column of values I want to plot -- if negative, then red; if positive, then green.
[tt]
=if($A1<0,A1,"") =if($A1>0,A1,"")
10 10
-5 -5
[/tt]
column a has the values. plot columns b & c

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[dstilson]

Hi Skip

Thanks for the reply. I do use formulas to get the values I want to plot for columns MG,MY,MR such as =IF(E4>=F4,E4,NA()) =IF(AND(E4<F4,E4>E3),E4,NA()) =IF(AND(E4<F4,E4<E3),E4,NA()) to create my Green, Yellow, Red series of bars. I then use the stack chart to merge them together. But I also want to plot columns YG,YY,YR as stacked but next to MG etc. The stack chart will put the bars on top of each other, but I rather have them side by side.
Thanks again

Don

 

[SkipVought]

You want two stacked columns, side by side? In other words, BOTH stacked and clustered?

Cannot be done, without some trickery.

Trick 1 - TWO separate charts superimposed. Hard to allign.

Trick 2 - ALL stacked. Add another category for the other stack. Drawback: hard to appear grouped.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[dstilson]

Yes, that is what I'm looking for. Both stacked and clusterd together.

I also thought about the separate charts, but haven't tried yet. Will look into that.

Can Excel 2007 do what I'm looking for? Our company will be going to Office 2007 in 1st quarter.

Thanks again

 

[GlennUK]

Here's one way:

http://andypope.info/charts/stackside.htm

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[dstilson]

Thanks Glenn, The website gave me the information I needed.