Identifying Peaks in Data

[SportsScientist]

Hi,

I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.

Sprinting is defined as a speed of > 20 kph.

One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.

It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.

Any help would be much appreciated.

Thanks in advance.

[zbor]

Try this in C column

=IF(AND(B2>20;B3<=20);"Sprint";"")

This will give you Sprint at the end of the sprint.. But I hope you don't mind it?!

(Chane ; to , if needed)

Edit: and in C1 you can add: =COUNTIF(C2:C1000;"Sprint")
wich will give you number of sprints

[DonkeyOte]

It's not clear to me at least what you want to output...

Total Number of Sprints in one cell: =SUMPRODUCT(--(B2:B1000>=20),--(B1:B999<20))

[zbor]

Well, I couldn't figure this out so I write it with fancy Sprint version

[DonkeyOte]

If using 2007 you may be able to use COUNTIFS (note: not available pre XL2007)

=COUNTIFS(B2:B1000,">=20",B1:B999,"<20")

[squiggler47]

I'm thinking this might not answer the question

I am assuming a "Sprint" would be identified by a peak abouve 20, but either side of that peek would be less IE

10 21 10 - sprint

10 21 21 24 21 10 - sprint

10 21 21 21 21 21 21 10 - sprint

[zbor]

and in all 3 cases answer is 1?

That's it

[DonkeyOte]

Not entirely sure I follow re: problem... the examples provided thus far would count correctly... the only anomaly would be

10,15,22,23,24,17,15,20,21,24,25 End

ie as to whether or not the last trend should be ignored given there is no sub-sprint value post 20 increase ... I would expect the answer to be 2 peaks which is what the formulae provided would both return.

Am I missing something ?

[SportsScientist]

Hi,

Thanks for all your replies.

The formula provided by zbor does work in the example I provided. But may not in relation to DonkeyOte post.

I'm not sure it will be a problem DonkeyOte beacuse after a sprint an athlete must decelerate thus at some point speed will drop below the cut off point.

Another interesting point is a "sprint within a sprint". Ie. 20, 23, 26, 23, 20.5, 24, 25, 26, 18, 15, 12.

I think I would class that as one sprint. But is there a way to mark that up as another sprint?

Thanks again,

P.S. I have some more interesting questions. I'm just working out the best way to articulate them to you!

[squiggler47]

this will identify peaks above 20

Please Login or Register  to view this content.

will allow a minimum change by changing tol to 0.1 will allow for minor variations

=

Please Login or Register  to view this content.

used in conjunction with donkeys will give you sprints within sprints

Mini sprints

Please Login or Register  to view this content.

identifies the 1 minisprint

(sorry donkey i see logic now, brain fried just spent 2 hours cleaning house!!)

[SportsScientist]

Dear all,

Thanks for all your help.

I would now like to identify a "sprint" by using another method.

The rate of change in speed over 1-second.

A sprint should be identified by a change of 5 kph over the last second. I've attached an example with my poor effort.

The problem with my countif formula is that it counts any cell in the range over 5. You can see that in cells D78 and D79 that the values are over 5 and so the the formula counts "2 sprints" when in reality it is part of the same singular sprint.

Any additional help would be much appreciated.

Thanks in advance (again)

[zbor]

Actually... I'm not sure what I've got... But better something than nothing )

Please, try this formula and see is this ok?

=IF(B2>C1;IF(B2-C1>=5;"Sprint";C1);B2)

Also.. in C1 MUST BE =B2
and in C2 and down write formula