Using If to change background colour of bar chart

[Anonymous]

I have the following expression applied to the background of a bar chart -

If(Year='Target 2014-15', lightred(), color(1))

It changes the colour of the bar to red for my 2014-15 target.  I want it to do the same when Year = 'Target 2015-16' and 'Target 2016-17'. Essentially I want the targets to appear a different colour to the actual historic Year values.

How do I write the code to do this?

Thanks

Greg

[MK_QSL · Accepted solution]

If(Year='Target 2014-15', lightred(), If(Year='Target 2015-16', green(), color(1)))

or

If(Year='Target 2014-15' or Year = 'Target 2015-16', lightred(), color(1)))

[Anonymous]

if you want red for all 3 targets

if (year='Target 2016-15' and year='Target 2015-16' and year='Target 2016-17', lightred(), Color(1))

if you wsnt other colours

if (year='Target 2016-15', lightred, if year='Target 2015-16, lightgreen, if (ýear....

thats what you want?

[Anonymous]

Thanks Manish and Rudolf.  It is working now thanks to your solutions.  I wonder also if there is a way to do this when Year starts with 'Target...'.  That way I can future proof the code for when future targets are added.

[Anonymous]

in my solution it should be "or" instead of "and". sorry.

to your question: you can use

if (left(year,6)='Target', lightred

[Anonymous]

Excellent!  Thanks Rudolf.

Greg