Find numbers lower / further than the previous (Track & Field)

[Mal/Betty]

I'm new to Office 2007, so am a little vaig on what to do. I'm trying to
automate a Recod of Events (Times/ Distances) in Ecxell but can not get it to
do what I want. Each time a Persanal Best is attained, all the functions I've
tried would only tell me it was faster/further than the first time/distance,
not the previous best.

 

[Satti Charvak]

Try using the

"rank" function.....it will give you the rank of all the timings.. Also, if
you can post some temp data I can make the proper formula for you.

 

[Shane Devenshire]

Hi,

A little vague on the data layout.

Suppose you run (ha!) one set of data for an event across a row with the
better results to the right. Suppose your data for an event starts in D2 and
you want the improvement to show in B2. Later performances going to the
right of D2.

Suppose the event you are tracking is javelin distances then your formula
would be
=LARGE(D2:N2,1)-LARGE(D2:N2,2)
Where you can increase N2 as much as you like.
If you were tracking something like time to swim a mile you could use
=SMALL(D2:N2,1)-SMALL(D2:N2,2)
You can handle both with one much more complex formula
=ABS(LOOKUP(10^10,D3:N3)-INDEX(D3:N3,1,MATCH(MAX(COLUMN(D3:N3)*(D3:N3<>"")),COLUMN(D3:N3)*(D3:N3<>""),0)-1))

This last formula is an array which means to enter it you press
Shift+Ctrl+Enter

If these help, please click the Yes button.

Cheers,
Shane Devenshire