countif error

iain401

New Member
Joined
Jun 23, 2007
Messages
31
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
Thanks Guys,

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

Iain
 
Upvote 0
=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))
 
Upvote 0

Forum statistics

Threads
1,217,256
Messages
6,135,499
Members
449,943
Latest member
thsix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top