Excel help please

[#1]

I help my running club with doing their racing results, but I am stumped by a problem I have discovered in an Excel database. To try to give the slower runners a chance, we 'handicap' all of the runners based on their past performance over the previous 5 years. As I enter the latest results onto this database, it may or may not ask about a runners fastest time in the last 12 months, or the last two years, or the last 5 years (it will want that fastest time and the date). I never know who it is going to ask about, nor which period it will ask about. To be able to answer these questions, I have produced an Excel database that covers the last 5 years, and as we do another race, I add that, and delete the data that is more than 5 years old. This gives me a lot of data to print off each month so that I can look for the answers to those questions. To make it easier, I introduced 6 columns where I had formulae that would hold the answers (time and date last 5 years, 2 years, etc). This works fine, making getting whichever answer easy to find, but the formulae have to be able to cope with cells that have no times in them because the runner didn't run in a race. If a runner hasn't run in any races for the last 12 months, the answers will show as '0:00:00' for the time, and '#N/A' for the date. These start to clutter the printout of the database, so I have been deleting the formulae for that runner for the two '12 months' answers (the 2 years and 5 years answers stay until they in turn go to 0:00:00, etc).

The problem I have just found is when someone restarts running these races after they have been away a long time. There is now no formulae to pick up their new times so my 'answers' have been missing the more recent times for them.

My formulae for the 12 month answers at the moment are as follows:-

=MIN(X7:AA7)

=INDEX($X$2:$AA$2,MATCH(MIN(X7:AA7),X7:AA7,0))

Is there anyone out there who could show me how to modify these formulae so that when the first formula comes up with '0:00:00' it shows on the printout as blank, but the formula is still there waiting just in case the runner restarts (ditto for the second formula, except that should be when it comes up with '#N/A'). I want these formulae to show the correct answers when the times are not 0:00:00, etc

Thanks for any help

[#2]

I'm no Excel expert (dabbler only ) but I'd say you need to use an IF function, and to print a blank for a result of '0:00:00' you would use "".

Not a very clear explanation I know, but might help you a little. 

[#3 · Accepted solution]

=IF(MIN(X7:AA7)=0,"",MIN(X7:AA7))

=IFERROR(INDEX($X$2:$AA$2,MATCH(MIN(X7:AA7),X7:AA7,0)),"")

Should work.

The first checks for a zero minimum time and inserts blank into the cell if it finds it otherwise it inserts the Minimum value.

The second will insert a blank if the formula returns an error (#N/A) otherwise inserts the  value of the formula.

You might need to tweak things a bit, I am assuming that "no"  time is held as zero and formatted as a time. If it's held as a string, then you may need to use something like '0:00:00' as the match for the IF statement.

[#4]

Thanks to both of you for helping. Wisty's solution did the trick. I had tried the IF type formula as mods to my formulae, but not the IFERROR. What I couldn't figure out was how to represent the '0:00:00' and the 'N/A' in the formulae, and the various guides I tried on the web didn't help.

Anyway, thankyou both once again

[#5]

@penneck - Does this help you?

The cells in Column B are formatted using the time format.

[#6]

Hello Mook,

Not sure your suggestion does help. I'm a little puzzled trying to understand what it is doing. Also, you appear to have turned my order of times through 90 degrees (my times would have been horizontal for each runner, with the race dates in the top row). Using your example (but not your values), each runner would have had a column (from Column B onwards), and each race would have had a row (say, Row 7 to Row 100). Column A would have the dates of the races (in Row 7 to Row 100). Therefore, what I would have been trying to do would have been look for the lowest (quickest) time in each column (Column B onwards), in the range (for example) Row 50 to Row 100, and putting that time into Row 1 of that column, and the date from Column A from the row that the lowest time had been found in into Row 2 of that column. Note that where a runner hadn't run a race, the cell in that column on that row would be blank. That is what the formulae in Rows 1 and 2, Columns B onwards were trying to do. For example, in Rows 50 to 100, say the lowest time in Column B was in Row 93, the formula in Column B, Row 1 would find it and the content of Column B, Row 93 would be copied to Column B, Row 1. Then the formula in Column B, Row 2 would find it and the content of Column A, Row 93 (the date) would be copied to Column B, Row 2. Thus, I can easily see what a runners quickest time and when the runner did it in the period covered by Rows 50 to 100. However, if the Runner in Column B hadn't run since the race in Row 30, he or she wouldn't have any times in Rows 50 to 100, so I wanted Rows 1 and 2 in that runner's column to be blank without actually getting rid of the formulae. That, basically is what wisty solved for me.

But thanks for trying. I'm always grateful for any help

[#7]

Apologies for clouding the issue @penneck I was trying to help where your last comment said you couldn’t figure out how to represent the ‘0:00:00’. The formula you can’t see in B1 is the same for B5 with the exception of the range covered being A1:A4, the values shown in column A are the number of seconds taken to run the race.

[#8]

Why not just put a "very large time" in the did-not-run races, so that will never be chosen as a best time.

[#9]

Because, over a 5-year period we may have had more than 100 different runners. Most of them wouldn't complete 5 years for various reasons. Putting a large time each race for every runner that hasn't run is a big job I could do without - don't forget I would have to do this even for those that had left us 4 years and 11 months ago (that is 59 large times just for that runner - doing similar for other runners is a lot of work on top of what I have to do already).

Finding the quickest time for each of those runners in that 12-month/2-year/5-year period is easy. The problem I had was when they stopped running for more than a year then started again. After not running for a year, I would have to delete the formulae that searched for their best time in the last 12 months (similar problem for both the last 2 years, and 5 years). If I didn't delete the formulae as these became 'did not run', meant that the printout was full of clutter making it difficult to read, so I removed any that were in that condition. If the runner then restarted doing these races, the formulae for their 12-month performances (and later, the 2-year and 5-year performances) would have gone, so I may not notice their new times weren't being picked up even though the times had been put in the table.

[#10]

This example cries out for a database. Especially an SQL database.

Excel gets used for all kinds of inappropriate things.

[#11]

@penneck, I have to agree with @VileReynard on this one, it would make your life so much easier if you were to use something like Access  or the free version of SQL Server  you could integrate it directly into the spreadsheet.

With some VB code this would allow you to archive your run results at the press of a button keeping your excel file to enter the current race results. You would still have access to a runner’s best time as with a SQL backend you can have the query return the best date and time for a runner regardless of when that was and all of this, and more, can be automated.

Doing this may be out of your comfort zone, but I'm sure there are more than enough competent people here that would be willing to help you out if you needed it.

[#12]

I would have to learn how to use SQL first. So far, I have had no connection with it, apart from using the results of some other persons SQL handiwork. When I have some time to spare, maybe I will be able to look at SQL, but thanks for the suggestion.

In the meantime, my thanks to all for their help