• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change Chart series color based on a cell's value

C

cacos

Member
Hello everyone!


I'm struggling with the following situation: I have a column chart that changes dinamically, but always keeps 1 series (Series1) and 5 different points (from Strongly Agree, to Strongly Disagree).

Typically, Strongly agree goes green (rgb 166, 187, 39) and Strongly Disagree goes red (233, 73, 23).


The chart's values change from a named cell that allows the chart to update dynamically based on that value (SetVal). The thing is, I need that when a certain question is selected (SetVal is equal to X), Strongly Agree goes red, and Disagree green, i.e. colors to be reversed.


I've browsed on the forum and different excel sites but couldn't find the exact solution.


Does anyone have an idea on how to do this?


Thanks!
 
Check out this tutorial:

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


Cover columns and line charts, and how to color them based on value. the trick is using formulas to figure out which points should be plotted for each color.
 
Yess i've worked on that same technique, only that you can't display data labels (when they change, half will show #N/A.


And all I could do was camouflage the labels font to the same color of the series point, to hide the #N/A, but then when they have do values, data labels' font appears in colors.


Any ideas on how I can sort this?
 
Thanks Narayank!


I've created an example: https://docs.google.com/open?id=0B9nov_b3A5SvQ0lCMUprSklFenc


You'll see that the reversed colors technique works (Question: General), but I also need to see data labels. So the problem is that it shows those annoying zeroes on the bottom of each series point (agree, disagree, etc.)


In this case, when the question is "General", the colors should be reversed, so Agree and Strongly agree should show in Red, and not in Green as usual.
 
Hi ,


I am sorry but I completely missed out seeing your last post ! 21 hours have passed without any action from my side ; I can only say that I'll look into it tomorrow.


Narayan
 
Hi ,


I have gone through your file , and I am not clear on your exact requirement.


As far as the zeros are concerned , you can eliminate them by using a Custom Format of #,##0;;; where positive numbers will be displayed , but zeros will not.


As far as the colors of the bars are concerned , I think the way are at present is correct ; your first two questions are :


1. Feel the Attitude was OK


2. Feel the Performance was OK


In both of these questions , a Strongly Agree / Agree percentage is a good sign , and it is correct that these two values are displayed in GREEN.


Your third question , however , is :


3. Feel everything is WRONG


In this question , a Strongly Agree / Agree percentage is a bad sign , and it is correct that these values are displayed in RED ; changing this to GREEN would not be a good idea.


If this is not what you wanted , can you explain how you would like the General chart columns to be displayed ?


Narayan
 
Hi Narayank sorry for the late reply. This is exactly what I needed! I don't know why I didn't even think on creating a custom format.


Thanks you are great!
 
You must log in or register to reply here.
Back
Top