Home Forum Chat Forum todays excel question – Time trial spreadsheet.

Viewing 8 posts - 1 through 8 (of 8 total)
• todays excel question – Time trial spreadsheet.
• warton
Member

So,

I’m creating a spreadie to record TT results on, for my different events.

I want to compare my time against the winner of that event. easy enough.

but, I want to compare my times against my PB for a course, and my PB for a distance. so i have the columns:
time
course (for example M21)
distance

then the columns
time difference – Course PB
time difference – Distance PB

so, how would i search the columns to find the PB for a given course, or distance, and then compare the time field against it? I thought vlookup, but I don;t think that will do the job, as it doesn’t look to be dynamic. any ideas?

warton
Member

I think the issue, is getting the best time for a given course, or distance….

stevomcd
Subscriber

Vlookup is dynamic.

Check out the DMIN function too.

clubber
Member

+1 vlookup is dynamic

konagirl
Member

If you want to see the workings, you could do the following. On a second ‘PB’ sheet you could put each unique combination of “course” and “distance” in columns A and B and then in C you need to do an array formula using MIN and IF to get your personal best for each course and distance combination. Something like the following, pressing Cntrl+Shift+Enter to make an array formula, assuming your TimingsSheet has column A for “time”, B for “course” and C for “distance”:

= MIN( IF(AND(!TimingsSheet\$A\$1:\$A\$1000=\$B1, !TimingsSheet\$B\$1:\$B\$1000=\$C1) !TimingsSheet\$A1:\$A1000, 0) )

Then in your timings sheet you can do a vlookup or match-index to pick out the PB time.

warton
Member

cheers all, I want to learn the nuts and bolts of excel, and this could be a good starting point, konagirl I’ll check that out tonight…

konagirl
Member

DMIN that stevomcd suggested looks to do the same job but in a nicer syntax.

warton
Member

sussed it using DMIN, and a nice little PB area of the spreadsheet!

cheers!

Viewing 8 posts - 1 through 8 (of 8 total)

The topic ‘todays excel question – Time trial spreadsheet.’ is closed to new replies.