Counting True Statements

[SportsScientist]

Hi,

I've collected some GPS data of an athlete running and want to quantify both the number and duration of individual sprints.

In the attached xls file, there is a column for time, speed and another that returns whether the speed column is >18 kph or not. False is <18, sprint if >18 kph.

Each entry with a "sprint" means they are sprinting (running above 18 kph) and each entry is 0.2-s. Thus 5 entries added together = 1-s.

I want to know, not the total number of sprint entries but the number of sprint groupings for example.

false, false, sprint, sprint, sprint, false, false,false,false, sprint, sprint, false = two seperate groups of "sprints". I would therefore conclude that the athlete sprinted twice.

I also want to know the duration of the sprint for each of the groupings. For example based upon each entry being 0.2-s, the first sprint grouping would total 0.6-s and the second grouping 0.4-s.

Any help would be much appreciated,

Thanks in advance

[JBeaucaire]

Here's a formulaic approach. In D2 add this:

=IF(C2="sprint",IF(C3="false",A2,IF(C1="sprint",D1,A2)),"")

...and E2 add this:
=IF(D1="","",IF(N(D2)>N(D1),D2-D1,""))

Then copy those two formulas down. The start and ending times for sprints will appear in column D and the durations in column E.Attachment 43005

[modytrane]

Here's another approach.
Unhide columns D and E to see the intermediate results.
modytrane

[martindwilson]

and another

[SportsScientist]

Hi everyone,

Thanks for your replies, they're all great!

Cheers

[SportsScientist]

Hi modytrane,

I've tried to modify the formula to take into account all the data ie. up to 15003 by replacing E$1:E$2000 with E$1:E$15003 but it's just coming up with VALUE in column F!

Could you help please?

Thanks in advance

(I havn't attached the file beacuse its a massive 3mb!)

[ChemistB]

The formulas in Col G are array formulas and need to be entered with CNTRL SHIFT ENTER rather than a simple ENTER. Did you do that?

[JBeaucaire]

Rethought my approach after looking at the other formulas suggested and I realized only one key column is required and no array formulas at all. Simple MAX() and COUNTIF() formulas. Now only column D is doing the key work:

D3 copied down:
=IF(C3="FALSE","",IF(D2="",MAX($D$2:D2)+1,D2))

SPRINTS in G2 and copied down:
=IF(MAX(D:D)<ROW()-1,"",ROW()-1)

DURATIONS in H2 and copied down:
=IF(G2="","",COUNTIF(D:D,G2)*0.2)

[modytrane]

nice solution, JB.
modytrane

[SportsScientist]

Fantastic! Thanks for all your help.

I'm way out of my depth here so I need to ask two more questions.

1. How could I calculate the total distance covered during each sprint?

(Column C calculates the distance in (m) covered over 0.2-s for each entry)


2. How could I calculate the time between the sprints?

Again, any help would be much appreciated,

Thanks for all your help so far.

[martindwilson]

ive no idea why you used 0.2 in that formula
total distance in each sprint can be calculated by
=SUMIF(E:E,H2,C:C) e:e is the column containing 1,2,3,4.....
where H2 is the one you want to refer to and c:c is where the distances you want to sum are.
and between sprints just use a modified version of formula in col e to number the falses! see attached

[JBeaucaire]

This adds a simple SUMIF() formula:

=IF(H2="","",SUMIF(E:E,H2,C:C))