Line graph with different colours depending on criteria

[marc01]

Hi all.

I am looking to use a line graph to show momentum changes in a tennis match.
I have a column showing momentum change ( +1 whenever player A win's a point and -1 when player B win's the point). I have then used an IF function so show when player A wins the point but show NA () when player B win's. Then another column to show when player B wins the point and NA () when player A wins.

I have seen an example of this before where if player A wins the line in blue, if player B wins the line is yellow. Also the markers are either green if a winner is hit or red if an error is hit. I wanted to know if this is possible with Excel or if I would have to use power bi or the like?

Any help would be much appreciated.

Thanks.

Marc

 

[marc01]

Managed to find the example I mention above. Any ideas?

 

[Greg Truby]

Jon's got tons of stuff on this kinda thing.

This should get you started:

https://peltiertech.com/conditional-formatting-of-excel-charts/

Scroll down a bit to get to the section using line charts.

Then take a look at what Jon does here.

https://peltiertech.com/bar-line-xy-combo-chart-in-excel/

That will show how to run a combo chart. In Jon's example he's blending bars & lines. In your case you'd be looking to blend lines & lines, but same principles apply.

 

[marc01]

Brilliant, thanks Greg! I will take a look just now.

 

[marc01]

Hi Greg,

Again thank you for pointing me in the right direction.

I have managed to get the 'correct' visualisation for the bar chart.

(sorry can't seem to successfully load image)

Below is a small selection of the data layout used to create this:

[TABLE="width: 954"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD]Maia Lumsden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Set Score (xvalue)[/TD]
[TD]Momentum Total (yvalue)[/TD]
[TD][/TD]
[TD]Marie Serving[/TD]
[TD]Maia Serving[/TD]
[TD][/TD]
[TD]Server[/TD]
[TD][/TD]
[TD]IF(G3=$D$1,B3,"")[/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD]IF(G3=$E$1,B3,"")[/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Marie Benoit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[TD="colspan: 2"]Maia Lumsden[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD="colspan: 2"]Maia Lumsden[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="colspan: 2"]Maia Lumsden[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="colspan: 2"]Maia Lumsden[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="colspan: 2"]Maia Lumsden[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

However, not the same success with the line graph. I have changed the IF function to =IF(G3=$D$1,B3,NA())

Therefore the data is:
[TABLE="width: 897"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD]Maia Lumsden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Set Score (xvalue)[/TD]
[TD]Momentum Total (yvalue)[/TD]
[TD][/TD]
[TD]Marie Serving[/TD]
[TD]Maia Serving[/TD]
[TD][/TD]
[TD]Server[/TD]
[TD][/TD]
[TD="colspan: 2"]IF(G3=$D$1,B3,NA())[/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD="colspan: 2"]IF(G3=$E$1,B3,NA())[/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: right"]-1[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD="align: right"]-2[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]Marie Benoit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[TD]Maia Lumsden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[TD]Maia Lumsden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[TD]Maia Lumsden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Maia Lumsden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Maia Lumsden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

However, following the procedure set out, this is creating a line graph with 3 seperate lines! I have actually copied and used the same data on the Peltier tech blog, and it worked perfectly.

Sorry, as you can tell I am not an excel whiz, so suspect I am making a mistake in doing something simple.

Any advice?

Cheers,

Marc