Multiple Ranks in same cell

Hi, I have a spreadsheet for race times, positions, points and then championship points. I have just found out I can use Rank to automatically fill in the positions based on the fatest time. Is there a way to be able to, in another cell, Rank the riders based on their catagory.

eg.
A B C D E F
# Catagory Name Time Pos Catagory Position
1 Senior Bob 1:10 1 1
2 Senior Bill 1:12 3 2
3 Junior Ben 1:11 2 1

Above is what I would like. I have used Rank for the overall position but I would like in the catagory position column for the ranks to be separate based on the catagories.

I have solved this by adding a hidden columns in between E and F (One saying - (If B2=Senior,D2,"") and the other Ranking this column. Then by doing the same for Junior I can split the ranks) As I have 5 catagories this adds 10 columns, then 6 rounds if the championship means 60 additional columns.

Is there a way of doing this in one column, or at least less than 10?

Thanks for your help, KAL46

Hi FDibbins

Thanks for replying. I have quickly editted and attached a file. Hopefully all the notes within it explain what i'm trying to do. It makes sense to be but i've been staring at it for hours :-) Let me know if I need to clarify it more.

Its quite in depth for a simple task but its as much a learning exercise as anything else so I would appreciate some tips.

Thanks again, KAL46

Hi again,

The IF formula should test both are blank. Thanks for pointing out about the F7, I have now changed this to =IF(AND(F7="",G7=""),"", MIN(F7,G7)) and it works. (Its just so that with no times you don't see an error)

Thanks also for the ranking. I changed the time and a tie break works now. I have set the format to m:ss.00 but if you don't input the colon and decimal correctly it inputs a date or hours, even though thats not the format of the cell.

I have found a more efficient was of doing this. I managed to get the RANK into an IF statement.

Therefore I still needed to split the times into seperate columns, by using the following, for each catagory type.

 IF($C7="Advanced,L7,"")

Now with 5 columns with the times from each catagory in seperate ones I used the following, rather than 5 columns with one Rank in each. Still uses a lot of columns, but less than before.

 =IF($C7="Advanced",RANK(X7,X:X,1),IF($C7="Senior",RANK(W7,W:W,1),IF($C7="Junior",RANK(V7,V:V,1),IF($C7="Juvenile",RANK(U7,U:U,1),""))))

Thanks for the help, hope this thread can help others,
KAL46