Line graph with different colours depending on criteria

marc01

Board Regular
Joined
Sep 17, 2018
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Managed to find the example I mention above. Any ideas?


GetAttachmentThumbnail
 
Upvote 0
Last edited:
Upvote 0
Brilliant, thanks Greg! I will take a look just now.
 
Upvote 0
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:

Marie BenoitMaia Lumsden
Set Score (xvalue)Momentum Total (yvalue)Marie ServingMaia ServingServerIF(G3=$D$1,B3,"")
0-011Marie BenoitIF(G3=$E$1,B3,"")
0-000Marie Benoit
0-011Marie Benoit
0-000Marie Benoit
0-011Marie Benoit
0-000Marie Benoit
0-0-1-1Marie Benoit
0-000Marie Benoit
0-0-1-1Marie Benoit
0-000Marie Benoit
0-0-1-1Marie Benoit
0-000Marie Benoit
0-0-1-1Marie Benoit
0-0-2-2Marie Benoit
0-0-3-3Maia Lumsden
0-0-2-2Maia Lumsden
0-0-1-1Maia Lumsden
0-000Maia Lumsden
0-011Maia Lumsden

<tbody>
</tbody>

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:
Marie BenoitMaia Lumsden
Set Score (xvalue)Momentum Total (yvalue)Marie ServingMaia ServingServerIF(G3=$D$1,B3,NA())
0-011#N/AMarie BenoitIF(G3=$E$1,B3,NA())
0-000#N/AMarie Benoit
0-011#N/AMarie Benoit
0-000#N/AMarie Benoit
0-011#N/AMarie Benoit
0-000#N/AMarie Benoit
0-0-1-1#N/AMarie Benoit
0-000#N/AMarie Benoit
0-0-1-1#N/AMarie Benoit
0-000#N/AMarie Benoit
0-0-1-1#N/AMarie Benoit
0-000#N/AMarie Benoit
0-0-1-1#N/AMarie Benoit
0-0-2-2#N/AMarie Benoit
0-0-3#N/A-3Maia Lumsden
0-0-2#N/A-2Maia Lumsden
0-0-1#N/A-1Maia Lumsden
0-00#N/A0Maia Lumsden
0-01#N/A1Maia Lumsden

<tbody>
</tbody>

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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top