countif error

[iain401]

Can someone help me I can't think of a way to sort my formula

This is from a sailing results spreadsheet. Column J is the sailors result times and column K is their placings. I am using the formula below and this works fine if all the times are different. But if they are the same as in the example below it goes wrong, it should be as in column L


=1+COUNTIF($J$5:$J$20,"<"&J5),blank)
J................... K...........L
3286..............1...........1
3286............. 1...........1
3395..............3...........2
3932..............4...........3
4265..............5...........4
4428..............6...........5

Thanks

 

[Gates Is Antichrist]

Would you mind if it gave 1,1,3,4,5,6? That's how I always see sports reporting, and there's a simple formula to do that
J5=RANK($J5,$J$5:$J$20,1)
Note the last argument is any nonzero number. See XL help on RANK.

EDIT: Bonus: you don't have to sort this way! If you still want the leader(s) on top, you can alternatively sort the RANKs rather than the times.

 

[barry houdini]

Try

=SUMPRODUCT(--(J5>J$5:J$20),1/COUNTIF(J$5:J$20,J$5:J$20&""))+1

 

[iain401]

When I tried to use the rank function before it seemed to give an error when there were cell errors or 0. Countif seems to avoid this.
I'll need to check what way they want the results maybe 1,1,3,4,5 is alright, it just seemed wrong to me. If 1,1,3,4,5 is ok then I don't need to change anything

thanks

 

[iain401]

Try

=SUMPRODUCT(--(J5>J$5:J$20),1/COUNTIF(J$5:J$20,J$5:J$20&""))+1

Hi Barry

Thanks,

That works if I limit the range to the cells that have values. But where there isn't a result because there were less people sailing I have #value! in some of the cells and it gives #value! in all the cells
it gives an error. Is there a way round that?

Thanks

Iain

 

[barry houdini]

What's the original formula that returns a #VALUE! error? Probably better to amend that so that it returns a blank and then you can amend my original suggestion to ignore blanks, i.e.

=IF(J5="","",SUMPRODUCT(--(J5>J$5:J$20),(J$5:J$20<>"")/COUNTIF(J$5:J$20,J$5:J$20&""))+1)

Note: I agree with Gates, the "normal" way of ranking would rank the lowest 2 tied values as 1 and the next as 3.....but I believe this method is also used

 

[Gates Is Antichrist]

As to RANK, I don't see the issue with 0. You should even be able to put in a negative time for that matter(which would come in first, BTW).

Errors are no problem - I swim in them all the time. Just change
=RANK($J5,$J$5:$J$20,1)
to
=IF(ISERROR(RANK($J5,$J$5:$J$20,1)),"-",RANK($J5,$J$5:$J$20,1))
and RANK will correctly handle the valid numbers.

 

[Gates Is Antichrist]

Barry's right; I've seen it both ways, but IMO the RANK way makes more sense in this case. The question to me is: if two tie for first and then there's another, isn't he really in "third" place in the race?

OTOH when two have to share the first place trophy, I guess you'd give the second place trophy to Mr. 3. Hmm...

 

[iain401]

Thanks Guys,

I think I'll make certain what way they want the results before going any further

Iain

 

[barry houdini]

=IF(ISERROR(RANK($J5,$J$5:$J$20,1)),"-",RANK($J5,$J$5:$J$20,1))

Unfortunately this won't produce the desired results. If J5 contains an error value then it will return "-" .....but you'll also get "-" for every other row, number or not....because RANK errors with any error in the range.

As I suggested above, you'd need to amend the formulas that produce errors to produce blanks instead and then, assuming you decide to rank using the conventional method, use

=IF(J5="","",RANK(J5,$J$5:$J$20,1))