• 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.

Chart Help!

J

Jayadev Sarangi

Member
Hello, Please find below the problem description.


I have a line chart with marker. The position of the marker changes according to the change in data. I need your help in changing the color of the marker automatically as the data changes. Lets say the range is between (-5%) to 10%. If the value is less than (-5%) then the color of the marker will automatically changes to RED and if it is between (-5%) to 10% then the color would be YELLOW and if it is more than 10% then the color would be WHITE.


kindly advise.


Regards

Jay
 
Check out this tutorial on conditional charts:

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


Thanks for the link, but I wanted something else or may be I am getting it incorrectly.


Let me know if I need to upload any file so that it would be clear. Please guide me.


Regards

Jay
 
You need to write if statements. then make a series for each color you want. then when ur data changes the if statements do their work making the unneeded color's invisible and the one u do need visible.
 
Thanks for the update. I think I need to understand it better. Do you mean, I need to write IF statement in the data range of the marker? Let me know if I need to share the file.


Regards

Jay
 
The IF statements are in the worksheet/data. The trick is to use several formulas to create the data/series for the chart. You will create one series for each color. Data/series that are not needed will evaluate to #N/A and will not be charted. In the link I posted, if you scroll down to Conditionally formatted line chart, you'll see a good example of this.
 
Thanks Luke. I think I am not getting the point. Let me re-explain with the below example. The line graph below looks like this.


-----------/---------/---------()

Marker Marker Sliding marker

(-5%) (10%) (this should change as per value)


The sliding marker if it is towards right after (10%) then the color is WHITE.

The sliding marker if it is between (-5%) and (10%) then the color is YELLOW.

The sliding marker if it towards left after (-5%) then the color is RED.


The position of the sliding marker is changing as I change the value but I want the color also should change. Let me know if it is not clear, I need to upload the file.


Regards

Jay
 
The sliding marker is going to be generated by 1 of 3 series. The 3 series are for the 3 different colors. formula for WHITE series:

=IF(DataPoint>10%,DataPoint,NA())


formula for YELLOW series:

=IF(AND(DataPoint>=5%,DataPoint<=10%,DataPoint,NA())


formula for RED series:

=IF(DataPoint<5%,DataPoint,NA())


You can see that only one of these formula will display a value at any time, the other 2 will be N/A. Thus, when these three series are added to your chart, only one series will be visible at a time, and it will give the appearance that a single point is changing colors.
 
I am using for Project Management Gantt Chart which i found difficult to enter the schedule base on date instead of week ? Please help me to use date instead of week .
 
@saif

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking.

Perhaps you'd want to read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!
 
You must log in or register to reply here.
Back
Top