Duplicate Ranks

[ashg1990w]

Hi all,

Apologies if this has been covered but I couldn't find this particular issue anywhere on Google.

I have been using a ranking formula that ranks with criteria and eliminates duplicate ranks. It has been working fine for years now and all of a sudden I'm getting a duplicate rank even though the values that are getting the rank are not even the same!

I have tried variations of the formula including the SUMPRODUCT formula that is commonly found online for ranking without dupes.

Please see formula below:

=IF(OR($U4="",LEN($D4)>3),"",COUNTIFS($D:$D,"<>????*",$K:$K,$K4,$U:$U,">"&$U4)+COUNTIFS($D$4:$D4,"<>????*",$K$4:$K4,$K4,$U$4:$U4,$U4))

Any help would be much appreciated!

 

[ashg1990w]

Hi all,

Apologies if this has been covered but I couldn't find this particular issue anywhere on Google.

I have been using a ranking formula that ranks with criteria and eliminates duplicate ranks. It has been working fine for years now and all of a sudden I'm getting a duplicate rank even though the values that are getting the rank are not even the same!

I have tried variations of the formula including the SUMPRODUCT formula that is commonly found online for ranking without dupes.

Please see formula below:

=IF(OR($U4="",LEN($D4)>3),"",COUNTIFS($D:$D,"<>????*",$K:$K,$K4,$U:$U,">"&$U4)+COUNTIFS($D$4:$D4,"<>????*",$K$4:$K4,$K4,$U$4:$U4,$U4))

Any help would be much appreciated!

Please see screen print of the problem rank and the %s being ranked.

 

[jasonb75]

Formula looks fine, how do the percentages in column A of the screen capture relate to the formula? We might need the actual data rather than what appears to be a small extraction in order to analyse the problem here.

One thought that came to mind from looking at the first post was floating point precision, but the difference between the 2 percentages shown in the image looks too great for it to be the actual cause.

 

[ashg1990w]

Formula looks fine, how do the percentages in column A of the screen capture relate to the formula? We might need the actual data rather than what appears to be a small extraction in order to analyse the problem here.

One thought that came to mind from looking at the first post was floating point precision, but the difference between the 2 percentages shown in the image looks too great for it to be the actual cause.

Hi Jason,

A few minutes after posting I read about the floating point precision and I have rounded the %s to 14dp and it seems to have fixed the issue and most importantly not changed any other ranks. I'm still a bit confused about the whole floating point scenario though, firstly because I don't really understand the full theory behind it and secondly because as you say the percentages are quite different.

If you can explain it in Lehman's terms that'd be great!

And thank you for the response too, it's much appreciated.

 

[jasonb75]

In all honesty, it confuses me trying to figure out what how and why. I just know that it can cause problems with decimals that may be relying on a high level of precision.

Usually when problems like this come up it is one of the first things that I look for if there is nothing obvious wrong with the formula itself.

 

[ashg1990w]

In all honesty, it confuses me trying to figure out what how and why. I just know that it can cause problems with decimals that may be relying on a high level of precision.

Usually when problems like this come up it is one of the first things that I look for if there is nothing obvious wrong with the formula itself.

I hear that! It's very confusing.

Thank you for the help though, it's appreciated.

I'll do some more research when I have time and try to understand it.