Single line graph different colours

[marc01]

Hi all,

I have 2 criteria, and based on which one is rtue I would like the colour of a line on the line graph to change.

I am doing this for points in a tennis match to show the momentum changes.

If Roger Federer wins the point the line graph goes up by 1, is Novak Djokovic wins a point the line goes down by 1. This is simple enough, however I would like the line to be blue when Federer is serving, and red when Djokovic is serving.

I have columns of data detailing the server, and who won the point. I have also then created another column titled 'Momentum'. The values here go up by 1 or down by 1 depending on who wins the point. I can use this to plot a line graph but have had not luck in altering line colour to show who is serving.

Thanks

Marc

 

[Gerald Higgins]

Hi, I suggest you use two data series, with your data laid out something like this

FEDERER........DJOKOVIC
1....................
2....................
3....................
2...................2 (at this point, the service changes from Fed to Djok)
.....................3
.....................4
3...................3 (at this point, the service changes from Djok to Fed)
4....................
3....................
2....................


and so on.

 

[marc01]

Thanks for the reply.

I have tried this in the past and it partially works....I get two different lines for each player when serving, however it leaves a gap between the points when they stop. For example when Federer stops serving with a data point at 2....if won the point to make the data point 3 this would be the score when Djokovic starts to serve. Therefore Federer line finishes at 2, but Djokovic line starts at 3. I have tried to connect gaps in the select data option, however this connects gaps between the data within the array as opposed to the next piece of data.

I'm getting closer!

Thanks,

Marc

 

[Gerald Higgins]

If you need more help with this, can you post a SMALL sample of your data please, so that we can see what's happening ?

Be sure to include at least one of these changeovers.

 

[marc01]

Here is a small sample of the data:

Federer Djokovic
[TABLE="width: 179"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I can't seem to post an image on here, however as can be seen with the above data, the Federer first line will stop at -2, however the Djokovic line will pick up from -3, this leaving a gap between the two line.

Unsure if there is another way around it, but I think I have figured out a work around. Take the first value for Djokovic (-3 in the sample above), copy this and paste it in the Federer data series. This appears to join the lines together as would be expected.

Many thanks,

Marc

 

[Gerald Higgins]

Yes that's exactly it, as I said in post #2

Not sure how you're compiling your data, but rather than copying / pasting, you might be able to do something with formulas that makes the process more automatic.

 

[marc01]

yes I agree, think I will need to explore macros and see if that helps
Thanks again for all of your help, it is much appreciated.
Best,
Marc

 

[Gerald Higgins]

Hi, I don't think you need macros for this.

If your source data is exactly as described in post #5 , then you can stick in a couple of helper columns that take your data and add in duplicate values whenever the serve changes.
Then use the data in your helper columns to drive the chart.

 

[marc01]

Hi Gerald.

Ah okay I will give that a try and see what I can return. Suspect I'll need an array formula with INDEX,MATCH and ISBLANK functions. Might pester you again if my novice skill can't step up!

Best,

Marc