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:
- if you subsequently edit the appearance of that series (say, to change the line weight or colour) you lose the manual adjustment
- 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
- 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
- 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 (
