+ Reply to Thread
Results 1 to 11 of 11

Conditional formatting data points within a chart?

  1. #1
    Registered User
    Join Date
    02-27-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Conditional formatting data points within a chart?

    I need points that plot within a chart to change color based on a set value. For example: 0, then the circle is filled in with yellow color; -2, then the circle is colored pale red; -4 or greater, then bright red; +2, then pale green; +4 or greater, then bright green. Thanks much.
    Last edited by VBA Noob; 03-01-2009 at 05:11 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional formatting data points within a chart?

    You need to use multiple series to plot the various colours.
    And you use formula to test the original data and either use that value or NA() to surpress the point.

    http://peltiertech.com/Excel/Charts/...nalChart1.html
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    02-27-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional formatting data points within a chart?

    Thanks Andy, The chart is built with multiple series (there will be 10 at most). How do I write the formula to make the points change color? Should it be an if/then formula?

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional formatting data points within a chart?

    Yes you would need to test whether the value falls within a value range for the colour being plotted. As explained on the page I provided a link to.

    You have 5 colour bands so will need 5 additional series.

    Not sure how the 10 series fit's in. If all of your 10 series follow the same colour coding rules how will you be able to tell 1 data point from another?

  5. #5
    Registered User
    Join Date
    02-27-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional formatting data points within a chart?

    Thanks again - Something is still not right though. I've attached the spreadsheet in hopes that what I am trying to explain may be clearer. I need to plot X & Y values, and then change the point color based on equation (X-Y). I'm pulling my hair out . . . Thank you again.
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional formatting data points within a chart?

    The principle is correct but your ranges are confusing.

    you can not be greater than zero AND equal to it.
    4 to -4 overlaps with the first 3 groups.
    And your test of negatives is wrong. As -4 is less than -2 not greater than.
    So the formula in F5 should be

    =IF($D5<F$2,IF($D5>=F$3,$D5,NA()),NA())

  7. #7
    Registered User
    Join Date
    02-27-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional formatting data points within a chart?

    I've updated the ranges to make sense. The chart is not plotting my X & Y values though, but the Y & "color code" value. It is coloring it properly against the ranges, but I need it to plot against one se of numbers, and then color against a 3rd.
    Attached Files Attached Files

  8. #8
    Forum Contributor ptm0412's Avatar
    Join Date
    04-16-2008
    Location
    Vietnam
    MS-Off Ver
    Office 2003 and 2007
    Posts
    129

    Re: Conditional formatting data points within a chart?

    Sorry, my English is not very good.
    Perhaps what you did is some values donot appear and you want all of them must take each value a point on chart?
    If so, please see my chart.
    Attached Files Attached Files
    Oldman Chatting: [email protected] Mailing: [email protected]

  9. #9
    Registered User
    Join Date
    02-27-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional formatting data points within a chart?

    Thanks for replying. What I want is for values to appear based on X & Y, but to change colors based on Z.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional formatting data points within a chart?

    You need to change your formula.

    =IF($D5>=E$2,IF($D5<=E$3,$C5,NA()),NA())

    This will test for the values being between or equally to the min/max values.
    If the difference value falls within the range you need to output the Y value rather than the difference value.
    Attached Files Attached Files

  11. #11
    Forum Contributor ptm0412's Avatar
    Join Date
    04-16-2008
    Location
    Vietnam
    MS-Off Ver
    Office 2003 and 2007
    Posts
    129

    Re: Conditional formatting data points within a chart?

    Now I can understand, so change the formula
    =IF(AND($D5>=E$2;$D5<=E$3);$D5;NA())

    to be
    =IF(AND($D5>=E$2;$D5<=E$3);$C5;NA())

    then it would correct.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1