1. change time formatting 2. frequency of blocks of cells

[marc01]

Hi all,

I am having a bit of an issue while trying to analyse data I have generated from using Dartfish (sports analysis) software.

Firstly, I am analysing a tennis match and with the software there is a score panel tool which allows me to track games, sets, and tiebreak scores. When exported to excel the data given for example is '01:03' or '04:02'. and is therefore formatted as time (and in some cased date). In this example I would like to split the data to show 1 game to player A and 3 to player B, and also have a column for overall score e.g '4-2'

I have tried to change or clear formatting, change text to columns, search and replace, however it does not seem to work (converts to 01:03:00, or 0.6699, for example).

Secondly, I would like to track the number of points a player wins consecutively. The data I have would read something like this:

Point won by
Player A
Player A
Player B
Player A
Player B
Player B
Player B


Here I would then want to calculate the number of times a player won just 1 point, 2 in a row, 3 in a row, 4 in a row, and 5+ in a row. I have been playing about with FREQUENCY, SUM AND COUNTIF functions but unable to make it work.

Any help/ guidance would be much appreciated.

Kind regards,

Marc

 

[Kamolga]

If 01:03 is in A1,

=LEFT(HOUR(A1),2)

will get you 1 and

=RIGHT(MINUTE(A1),2)

will get you 3, so

[LEFT][COLOR=#222222][FONT=Verdana]=LEFT(HOUR(A1),2)&"-"&[COLOR=#222222][FONT=Verdana]RIGHT(MINUTE(A1),2)[/FONT][/COLOR][/FONT][/COLOR][/LEFT]

should get you 1-3

 

[marc01]

Brilliant thank you very much for your help! Excel never ceases to amaze me at the moment

 

[Kamolga]

You are welcome, thanks for the feedback