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

 

[iain401]

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

Hi

I need the results to be 1,1,2,3,4, So I need to use your formula above. I'm not too sure how to get my other formulae to return a blank rather than an error though.
The formula is
=G5*1000/(H5+I5) G5 is the elapsed time and H5 is a boat handicap figure and I5 is a personal handicap. In rows that have no entry boat handicap is #N/A, Personal handicap is blank and Elapsed time is #value.

Thanks for any help you can give

 

[barry houdini]

Try

=IF(ISERROR(G5*1000/(H5+I5)),"",G5*1000/(H5+I5))

 

[iain401]

Try

=IF(ISERROR(G5*1000/(H5+I5)),"",G5*1000/(H5+I5))

thanks works a treat and now that I can see it I can see how it works. I guess I'm just not familiar with these functions yey

Thanks

 

[Gates Is Antichrist]

=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.

I don't know, Barry - you might test that. It works below even WITHOUT error checking (column N)

rank.xls
	J	K	L	M	N	O
5	3286	1		3286	1	1
6	3286	1		3286	1	1
7	3395	3		3395	3	3
8	3932	4		3932	4	4
9	4265	5		Barry!	#VALUE!	-
10	4428	6		4428	5	5
Sheet1

 

[Gates Is Antichrist]

Sorry about the column widths; me.Colo.newbie=true. I autosized before HTMLing. Tips welcome.

I also get one preview, but can't submit or preview again if I previewed first. And it's messing with IE6 offline status too.

 

[barry houdini]

....perhaps a misunderstanding.....

I believe that Iain suggested he had errors in the rank range, i.e. #VALUE! etc. in the range J5:J20. Any single error in that range and the formula

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

will just give "-" for every cell down the column, which is why I suggested trying to replace the error values with blanks.

In your example you have text in the range but not an error value. If that were the scenario, and you wanted to rank in the conventional manner, I would suggest

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

 

[Gates Is Antichrist]

You're very correct - good work.