Chart changing bar color based on value when compare to secondary value [SOLVED]

[ireland13752]

Good afternoon. Thank you for taking the time to assist me.


I have a chart that I need assistance with please. My chart shows two data points,


1. Actual Score

2. Goal


I have 33 different departments that I am tracking, and need for them to reflect in the chart when we meet or exceed the goal. So if the continual goal is 90% and in April we hit 75% then the graph bar is Red but if in May we hit 93% then the bar in the graph changes to Green. I'd prefer that only the part exceeding the goal changes color if possible. I tried Gradient fill, but that changes all bars regardless of them meeting the goal.


I read through several posts in the Charts forum, but was not able to use any of them to get what I need. I appreciate any and all support.


I have an example spreadsheet I created to upload for visual, but am not sure how to do that either...


Thank you

Ireland13752

 

[jeffreyweir]

ireland: see http://chandoo.org/forums/topic/posting-a-sample-workbook

Jon Peltier has many tutorials on formatting charts. You might want to google his name and your question, and see what comes up.


e.g. http://peltiertech.com/WordPress/conditional-formatting-of-excel-charts/

 

[Hui]

Ireland


This can be done simply by using a stacked Column chart and error bars


Setup 3 series of data to represent

1. The Value if < Goal

2. The Value between 1. and the Goal or the Goal if the Value is > Goal

3. the Value > Goal if greater than Goal

Setup as a Stacked Column chart

Format Series 2 as No Fill or Outline

Then add Horizontal Error Bars with offset 0 to series 2


Or see an example here:

https://www.dropbox.com/s/hpq8ob3x9d2r8qk/Ireland Chart.xlsx

 

[ireland13752]

This is fantastic! Thank you, I was able to take this detail and change it a bit to meet a new requirement. But just having this guide was exactly what I needed and fantastic! Thank you again for helping me.


Have a great day!