Countif not split a tie from rank function?

[as9178]

Hi,

Have highlight the cells in bright yellow which are tied for some reason?

Thanks

Andrew

[Sam Capricci]

your first formula in that column is wrong...=RANK(AO3,$AO$3:$AO$45,1)+COUNTIF(AO3:AO$3,AO3)-1
it should be this...
=RANK(AO3,$AO$3:$AO$45,1)+COUNTIF($AO$3:AO3,AO3)-1
then dragged down

[Pete_UK]

Check out Chip Pearson's site here:

http://www.cpearson.com/Excel/rank.aspx

and scroll down to the section on "Unique Ranks in Ascending Order". You will see that the formula (and logic) is slightly different in this case.

Also, note that your formulae in column AO refer to some external file, which we do not have access to.

Hope this helps.

Pete

[as9178]

your first formula in that column is wrong...=RANK(AO3,$AO$3:$AO$45,1)+COUNTIF(AO3:AO$3,AO3)-1
it should be this...
=RANK(AO3,$AO$3:$AO$45,1)+COUNTIF($AO$3:AO3,AO3)-1
then dragged down

Are you sure? Still getting a tie

[Sam Capricci]

I've tried three different rank formulas including changing the order from ascending to descending and you still end up with a tie. I know I saw a sumproduct used for this once but I cannot remember how it was written. Maybe someone else can come up with a way to break the ties. (I've never seen it not work before.)

[Ace_XL]

Try in AP3

=SUMPRODUCT(--($AO$3:$AO$26<AO3))+COUNTIF($AO$3:AO3,AO3)

or just

=COUNTIF($AO$3:$AO$26,"<"&AO3)+COUNTIF($AO$3:AO3,AO3)

Copy down

[Sam Capricci]

@Ace_XL, nice, I knew there was a sumproduct for it, I'm assuming to get them in descending order you change the "<" to ">".

[Ace_XL]

@ Sambo, Yes!

These links might be helpful too

https://www.mrexcel.com/excel-tips/1...-of-precision/
https://docs.microsoft.com/en-us/off...ccurate-result

[Sam Capricci]

it didn't seem like a floating decimal issue to me when both values being ranked were so different.

[as9178]

Try in AP3

=SUMPRODUCT(--($AO$3:$AO$26<AO3))+COUNTIF($AO$3:AO3,AO3)

or just

=COUNTIF($AO$3:$AO$26,"<"&AO3)+COUNTIF($AO$3:AO3,AO3)

Copy down

Many thanks. Worked perfectly