Wednesday, September 30, 2009

Two Color XY -Area Combo Chart

I was reading a post on the PTS Blog about how to fill in the area between two lines in an excel chart and I thought of a way to make it more complicated and thus more intimidating to your coworkers.

The original article shows how to color the area between two lines in a chart where the lines never cross.  I'm going to assume you're like me and you read that blog every day so you already know what I'm talking about.

What if you want to cross the lines?  The good news is that crossing the lines doesn't break anything, the area between the lines is still filled in perfectly.  The bad news is that the fill series is only one color and as soon as you show it to someone they'll ask why the positive and negative areas aren't green and red, respectively (which you shouldn't do anyway because a lot of people are red/green colorblind).  Before today, you would have said that it would take too long to add a second color, that it was impossible, or you would have just drawn auto shapes on top of the chart.  Today though there is a much easier solution:
  1. Download the workbook at the end of this post.
  2. Plug in your data values.
  3. Profit.
If you actually want to know how the chart works, please read on...

I'm assuming we're all starting from this point:
Okay, so the first thing that is obvious is that you'll need to add another data series if you want an additional color.  Creating separate positive and negative data series doesn't work by itself though, you need your positive and negative shading to end where the lines intersect, but the shading will continue on to the next point on the axis with the current configuration.  What we need to do is find the points where the lines intersect and add them to our secondary x-axis (just a recap, the lines are on the primary x-axis, the shading on the secondary axis).

This isn't as hard as it sounds, but when searching for a way to find the point where lines intersect in Excel I found a lot of people suggesting Goalseek.  Letting machines do math for you is only okay if you told it exactly what to do.  If you didn't tell it what to do, the computer is thinking for you, and that is a slippery slope to Skynet.  But that's a whole different post.

The common formula for a straight line is y = mx + b, where m is the slope and b is the y-axis intercept.  At each point of intersection, we have two lines:

y1 = mx1 + b
y2 = nx2 + c

Because we want the point where the lines intersect, y1 = y2 and x1 = x2.  Because both of the formulas equal y, they are also equal to each other and we can simplify to:

mx + b = nx + c
Solving for x gives us:

x = (c-b)/(m-n)

Excel has built in functions for calculating the slope and the intercept for a range of x and y values which they've cleverly named SLOPE() and INTERCEPT().  We'll only need two points to calculate each of our line segments, so our formulas will take the form

slope({y1,y2},{x1,x2}),

where (x1,y1) are the coordinates of the beginning of the line segment and (x2,y2) is the end point of the line segment.

Because the lines can potentially cross between any two data points, we're doubling the size of the original secondary x-axis.  Every other point will be one of the original data points, and the in between points will either be NA() if the lines don't cross (Excel will skip over NA() errors when plotting most types of charts) or something like this formula if they do cross:

=(INTERCEPT(c4:c5,d4:d5)-INTERCEPT(b4:b5,d4:d5)) /
(SLOPE(b4:b5,d4:d5)-SLOPE(c4:c5,d4:d5))

Of course the formulas in the workbook are much uglier because I'm counting ROWS() and using the OFFSET() formula so I don't have to manually add each line segment from the original data set, but it's a little hard(er) to follow.
Your new secondary x axis should look something like column G.  You stil run from 0 to 1,000, you still repeat the first and last value, and every other point is one of the original points as calculated in Mr. Peltier's post.  The points in between are #N/A errors where the points don't cross, and the points of intersection we calculated with the INTERCEPT() and SLOPE() formulas if they do.  An easy way to tell if the lines cross?  In column F we had previously calculated the difference between the two original data series.  If the product of any two adjacent points in column F is negative, the lines intersect between those two points.

The next step is to rebuild the three Area data series.  Area1 is the transparent series , Area2 is the positive series, and Area3 is the negative series.

To create Area1, every other row should point to an entry in the original series in column E.  The in-between points should once again be NA() errors if the lines don't intersect (same for Area1 and Area2 as well), and when they do intersect we just need to calculate the y value at that point.  We already have the x value, so we can plug that into our y = mx + b equation.  The formula for H9 would be:

=SLOPE(b6:b7,d6:d7)*g9+INTERCEPT(b6:b7,d6:d7)

You could also use the points c6:c7 for your y values as both line formulas will give the same result with that x value (g9).

Area2 and Area3 are incredibly straight forward.  We still use the points in column F for every other entry, but the trick is that we only use the positive points in Area2 and only negative points in Area3.  This is easy to do with MAX(f4,0) for Area1 and MIN(f4,0) for Area3.  All other points are either NA() errors if there is no point of intersection or zero.

Now you just need to go into your graph and add these series to the chart.  Series 1 and 2 are lines, Series 3 (the transparent series) should now point to h2:h20, Series 4 (the positive series) should point to the values in column I, and you'll have to add a Series 5 (negative) to point to the values in column J.  The secondary x-axis values also need to be changed to the values in column G.  Also, if your Series 5 didn't default over to the secondary axes, you'll have to switch it.  Double click on each area series, change the colors, and you're all set.

If everything went correctly, you should have something like this:

If not, the workbook will make more sense than I ever could:


Let me know what you think in the comments.

-David

38 comments:

  1. David -

    Thanks for elaborating on my technique. I've included your final figure into your comment.

    ReplyDelete
  2. any way to place this back on drop.io. I think the link is expired

    ReplyDelete
  3. Replies
    1. Hello David,

      Your site has been very useful, and I appreciate all of your tips and techniques. I want to make a similar type of chart as stated in your post. I have tried your technique as well as others, but it seems that I had made some mistake and I was struck in 2nd X axis calculation onwards. Please help me to solve the same at the earliest. Moreover I am not able to download the file from your link.

      Thanks in Advance,
      R.Vadivelan

      Delete
    2. hi david! thanks for this blogpost, however, the link is not working any more :( could you please update it? thanks a lot!

      Delete
  4. Trying to adapt it to a time series but hitting a wall...

    ReplyDelete
  5. plz David
    I want to file
    [http://drop.io/davidatwork/asset/twocolorxyareachart-xls] <-Do not go down

    Email me plz
    hday52@nate.com

    ReplyDelete
  6. Hi David, this looks great! I was trying to download the XLS file, but it times out.. Is the link still active?

    ReplyDelete
  7. link does not work.. can u fix please?

    ReplyDelete
  8. Hi David,

    Any way to place this back on drop.io. I think the link is expired again.

    Please?

    ReplyDelete
  9. I would like to setup a chart like this but am having trouble following the post, could you please repost the excel file? Thanks!

    ReplyDelete
  10. Any chance to provide the XLS again? Link seems to be expired again.

    Thanks!

    ReplyDelete
  11. Hi, thanks for this post, but the link is broken/expired again ...

    ReplyDelete
  12. Hey, could you update the link again? Thanks!

    ReplyDelete
  13. Link has expired :(

    ReplyDelete
  14. Hi David,

    Could you please update the link to the excel file?

    Thank you

    ReplyDelete
  15. Hi David, can you please be so kind to update the file again...it's expired.

    ReplyDelete
  16. Link does not work :(

    ReplyDelete
  17. Hey David, 2 years later and this article is still useful! Would you mind uploading the workbook again? it appears the link is no longer valid.

    ReplyDelete
  18. can anyone update this old link?

    ReplyDelete
  19. The link is broken, David. Great blog, anyway.

    ReplyDelete
  20. Link is not working...pls update

    ReplyDelete
  21. Please send me the file at niftymama@gmail.com

    ReplyDelete
  22. Would it be possible to place this back on drop.io yet again? The link has probably expired again? Many thanks....

    ReplyDelete
  23. Hi, it looks like the link has expired. Can you update it? Thanks!

    ReplyDelete
  24. I still cannot download the workbook. Any chance of updating the link?
    (Roger Berk)

    ReplyDelete
  25. link not working again... please update.

    ReplyDelete
  26. can you update the link possibly?

    ReplyDelete
  27. Hi, not sure if you're still monitoring this blog, but the link has expired again. Any way to reactivate it?

    ReplyDelete
  28. Any way to get this workbook? Link expired again. I guess I'm really really really late to the party.

    ReplyDelete
  29. Hi, I think the link to drop.io is expired again?

    ReplyDelete
  30. Hi,

    unable to download the file.

    ReplyDelete
  31. Any chance you can update the link again

    ReplyDelete
  32. Anyway to update the link... I know this is years later... but this is what I'm looking for. Thanks.

    ReplyDelete
  33. Hi David, I was referred here from Stephanie Evergreen's blog (EvergreenData), but your magic spreadsheet link has expired again. Is there any chance that you could re-link your spreadsheet?

    ReplyDelete
  34. Hi, could you update the link again? I'd love to be able to download the workbook. Thanks!

    ReplyDelete
  35. Good job- this sounds great! Could you update the link again, please? :)

    ReplyDelete
  36. So I guess the secret to getting people to read my blog was to stop updating it for six years. Who knew?

    The last time I was playing around with this file (Stephanie Evergreen asked me about it earlier in 2015), it was no longer working in Excel 2010. Attempts to revive it were unsuccessful, but my Excel skills are a little rusty these days.

    If you want to play around with it, Jon Peltier has a working link to the old file here: http://peltiertech.com/two-color-xy-area-combo-chart-guest-post/

    ReplyDelete

 
Creative Commons License
David @ Work by David Montgomery is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.