 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
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!