Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Charting - Line colour for above and below (XL 2K

    I was responding to a question in another forum: how can a line chart show different colours for values above and below a target value? It was much easier to deal with by attaching an example file along with the explanation. I am posting a message there referring anyone interested to this post, and my direct e-mail to the OP follows. I hope that some people here might get some value from it, as well.

    I am attaching a sample file, and I will try to explain the calculations since even with the sample it may not be easy. I have made a couple of changes since the post on Friday, first, to try and make it a little easier to follow, and second to set it up the way I think it would actually be used.

    The first step is to start with the actual data - I've highlighted that in light yellow in the range B4:C15 - and the target value - also highlighted in yellow in cell C18. I have put an example of the "finished product" graph over to the right, with the line segments above the target in green, below the target in red, and the target value in blue. To make it easier to discuss I have given each observation a letter, although they do not appear on the graph. You can see that the line colouring is live - if you change any of the values the line colours change appropriately - for example, if you change the Y-Value for point D to (say) 10 (or anything above the target value) the line segments C-D and D-E change from green/red and red/green, respectively to continuous green lines. As well, if you change the target value the lines will change colour as they pass across the target value line.

    So, okay: how is it done?
    A single line on a chart can only have one colour, unless you change it manually for a given line segment (or segments) - but that leads to a couple of problems:
    <UL><LI>if you subsequently edit the appearance of that series (say, to change the line weight or colour) you lose the manual adjustment
    <LI>you have to manually determine where the line should change colour - the whole idea is to make the computer do that sort of grunt work
    <LI>unless you are lucky, the segment won't end right at the target value - so you will have to either accept the "wrong colour" crossing the target value in one direction or the other, or calculate where the line should change colour and insert a new data point at that location, which will allow you to make the manual colour change at the right spot
    <LI>if the data or the target value changes then all the work will have to be re-done - as above, the idea is that the computer will be better at this than you (or at least, be a lower-cost resource)[/list]The solution involves plotting two lines - one for values above the target, and one for values below - this allows the formatting of the two series to be independent: one could be a red dashed line while the other was a blue solid line, for example, and changing one will not affect the other - and ideally if the data or target changes, the two series will reflect it properly. The two series are illustrated in the Working Copy graph - the green line plots values above the target (or Y-Values above the target), and if the observed value is below the target, it plots the target value; the red line does exactly the opposite, and it is pretty easy to see that applying a Max() or Min() function to the observed values and the target value will provide this sort of result. The Working Copy does not include the "target value" line, but that's just to make the two plotted series clearer.

    That still leaves us with one problem - as seen in the "Not Quite There" chart, the lines don't connect properly when the (Y-) values cross the target value. The lines are fine when the value stays on one side of the target (ABC, or IJK) but the line should go from C to D' - instead we have two lines, one from C-D and one from C'-D' - and we see the same problem at DE' and FG' and so on. To force the line segments in each series to line up, we calculate the point where they would each hit the target value (for example, the point on the line segment C-D' that would cross the target line) and then include that point in each data series. Since we don't know in advance where the (real) data will cross the target we have to calculate this mid-segment point for every successive data pair). The Working Copy graph includes the capital letter references for the real data, as well as small-letter references for these segment mid-points, identified as the small letter of the left data point on the segments joining each real data point, with a caret (^) to mark the mid-point for the "above" line segments and an inverted question mark (
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting - Line colour for above and below (XL 2K

    Thanks for the clever solution and comprehensive explanation!

    Just one small remark: the chart is an XY scatter chart, not a line chart. It must be an XY scatter chart because the interpolated points require that the x-axis is ordinal (numeric), not categorical.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charting - Line colour for above and below (XL

    Hans - right you are; while I am tempted to claim that I was merely testing to see who was paying attention, I have to admit that I just wasn't quite as clear as I should have been.

    If you needed this sort of effect for data that you intended to present as a line chart (say monthly values for some statistic) you would have to re-cast it as an XY chart using a proxy like the start of month or mid-month date.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting - Line colour for above and below (XL

    Yep.

    I'm certain that the method you describe will be useful to other Loungers! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charting - Line colour for above and below (XL

    People who went over to Mr Excel have already seen it, but if not, Jon Peltier (who is one of the real experts on charting in Excel) points out that Andy Pope had a very similar solution to the same problem although his approach uses a (very slick) UDF to calculate the cross-over points, and then pastes the appropriate values into the plot ranges on demand via VBA. One really nice thing about Andy's approach is that it allows an increasing or decreasing target value, which is pretty cool.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charting - Line colour for above and below (XL

    I doubt that anyone is following this saga, but over at Mr Excel Jon Peltier mentioned that he had implemented a solution similar to Andy Pope's, that would allow a sloping target line, but that he had done it via formulas rather than by a VBA routine. I don't think Jon intended it as a challenge, but I decided to take a whack at it out of interest - the result is attached.

    In effect the formulas apply a Gaussian elimination to the augmented matrix formed from the target line and the line defined by each successive pair of data points. There is a test for which line (if either) has a zero slope, since that would normally be the second row of the matrix as the "X" coefficient would be zero. There is still one (minor problem) - the formulas crash if the data line is vertical, since the slope is some value divided by zero. If I was providing this for a production environment I would probably insert a test for that condition - but for now I think I will leave it as is. The logical next step would be to allow a series of target lines, which could approximate a curve - but for that level of graphing / display, I think Excel might not be your first choice in any event.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting - Line colour for above and below (XL

    Thanks again!

Posting Permissions

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