todays excel question – Time trial spreadsheet.

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?

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

Vlookup is dynamic.

Check out the DMIN function too.

+1 vlookup is dynamic

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.

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…

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

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

cheers!