Mynda,
As you suggested in the comment on the blog on Multi coloured line charts, I am posting the sample file here.
I have not repeated mybackground comments from the blog but in summary we want the line colour to change to indicate good/ bad performance as this is up or down for different charts on the same dashboard. (here down is good = green; up is bad = red)
Having every value in the first green series plotted is helpful as per your blog, but due to the need to have overlapping values to plot both ends of the red lines I then need a second green series to overwrite some of the redlines.
Per the note in cell A1 of the workbook the issue is with the logic of the IF/AND/OR formulae for this series in row 9. I just cannot get it to deal with both inflexion points. The required output is noted in row 10 and whether my current formulae (i have tried many) gives an error or is OK is in row 11.
Also included is a picture of the chart in the desired state (I manually coloured the lines with only one series plotted).
Any suggestions welcome or could there be a different approach?
Steve
Hi Steve,
I think with this chart you'd be better off just using markers and no line. See example attached.
In the line chart I couldn't tell if 2014 was good or bad, likewise 2016. With the markers it's much clearer, although you might need to alter the formulas if 2014 and 2016 were 'good' years.
Mynda
Thanks Mynda - that is an interesting work-around, and yes 2014 and 2016 were 'good' years.
However, the trend of the accident frequency is also important and the multi-coloured line helps to get this across... I will continue the search to get an IF/AND/OR formulae which works, but if you have any thoughts that would be great.
Thanks again, really appreciate your help as always and enjoy what remains of your weekend!
Steve
Hi Steve
See if my examples help.
Some are not so elegant.
Sunny
Thanks Sunny - the VBA solution is neat... and impressive.
Trying to write logical formulae to do the same thing was doing my head in and taking a lot of time too.
Much appreciated and enjoy what remains of your weekend in Malaysia.
Steve
Hi Steve
I had to use VBA as I am unable to find any formula that would give me the same result.
If you find any, hope you will share it with us.
Cheers
Sunny
