Conditional Format Charts

This sounds very interesting... can you post some sample data and what condition would trigger the conditional format ?

I am actually trying to do the same thing with bar graphs. Depending on the value returned, the bar should be one of three colors (red if the value is less than 90, yellow if the value is between 90 and 95, and green if the value is greater than 95.). I have done conditional formatting on the cells where my formulas are so they come out the correct colors, I just don't know how to conditionally fomat the graph. I am currently trying to use macros to accomplish this, but now can't figure out how to run a macro in a function (the IF function, in particular.). Any assistance would be greatly appreciated. Thanks.

Now that I think about it, I've seen the title "Conditional Charts" on Jon Peltier's site...

Here's the link:

http://www.geocities.com/jonpeltier/Excel/Charts/ConditionalChart1.html

I've looked at Jon's site and I can't figure out how he's changing the colors depending on the value. In my case, I have a separate formula that places a value beside each data point source line:

if the value is 2 -> I want the data point green
if the value is 1 -> I want the data point yellow
if the value is 0 -> I want the data point red

These are the only possible values this formula will return.

Can you post some sample data that you're working with to know the structure of your sheet ?

Juan,

Thanks for your quick response. I've posted a sample below.

Branch Units Fixed Cost Net Revenue Conditional Formula

Branch 1 40 1337 4864 1 <--- yellow
Branch 2 50 856 3495 2 <--- green
Branch 3 14 1275 2602 0 <--- red

The bubble graph is based on the Units, Fixed Cost, and Net Revenue columns. Bubble size is determined by Units. Fixed Cost is the x-axis. Net Revenue is the Y-axis. The graph itself is broken into quadrants determined by goals.

The goal is 1200 for Fixed Cost. The goal is 3400 for Net Revenue. I've drawn lines on the chart to mark these goals. Thus is a branch is less than 1200 in Fixed Cost and greater than 3400 in Net Revenue (as is the case for Branch 2), I would like that data point to be green. The Conditional Formula result shown above just looks to see if Fixed Cost is below 1200 and Net Revenue is above 3400. The branch gets 1 point for each goal it meets. If it meets one goal only, it should be yellow. If it meets both goals, it should be green. If it meets neither goal, it should be red.

I have a sample chart I could send you, but I'm not sure how to post a file to this site.

Thanks again for all of your help.

Ok, the following is based on the methodology that Jon explains on that page I linked.

Let's assume the following data, based on what you posted:

Now, I apply some formulas, so I end up with this:

The formula in F23 is

=IF($E23=F$22,$D23,NA())

which is dragged down and across

Finally, I create a bubble chart that has three series. Its formulas are:

Serie 1:

=SERIES(Sheet1!$F$22,Sheet1!$C$23:$C$25,Sheet1!$F$23:$F$25,1,Sheet1!$B$23:$B$25)
Serie 2:

=SERIES(Sheet1!$G$22,Sheet1!$C$23:$C$25,Sheet1!$G$23:$G$25,2,Sheet1!$B$23:$B$25)
Serie 3:

=SERIES(Sheet1!$H$22,Sheet1!$C$23:$C$25,Sheet1!$H$23:$H$25,3,Sheet1!$B$23:$B$25)

Serie 1 is formatted with a Red background, Serie 2 with a Yellow background and Serie 3 with a green background.

You can remove the legend to avoid "issues", I just named the series for clarity.

Juan,

You're a genius! You don't know how much time this is going to save me.

Thanks so much!

Chris