Dynamic Tie Breaker Not Workign

[rawraw]

Hello!

I use this forum a ton, but rarely post. I'm trying to figure this out. I am using the rank + countif formula to do a tie breaker and then vlookup to rank. But for some reason I can't figure out, one of my data sets it has an error. I have narrowed it down to this: This is the rank without a countif

Rank Number
9 8.55
8 9.40
7 10.15
6 10.70
5 10.80
3 10.80
3 10.80
2 11.60
1 13.35

Notice the 10.8 is ranked 5 then 3, 3. If I set up a grid referencing the three 10.8's and using =Cell=Cell, I get this: (L6, L7, L8 are the cell references for the 10.8's)

L6 L7 L8
L6 TRUE TRUE TRUE
L7 TRUE TRUE TRUE
L8 TRUE TRUE TRUE

Any idea why rank would rank them different despite them being the same? The formula for rank I drug down is this:

=RANK(L6,$L$2:$L$10,0)
=RANK(L7,$L$2:$L$10,0)
=RANK(L8,$L$2:$L$10,0)

[JBeaucaire]

When I put those same values into a range L2:L10, I get 3 for all of their ranks. So there is an unseen difference in your data. Copy L7 and paste it into L6.

[rawraw]

Hey thanks for the reply!

I found the answer when I improved my google search of this forum.

Charles Blaquière wrote:

> Ron Rosenfeld wrote:
>
>
>>I cannot reproduce your problem with your posted data. I suspect the
>>two scores are NOT exactly the same on your worksheet.
>>
>>1. How are the scores calculated? If these are calculated rather
>>than simple entries of integers, it is likely that the two 19 point
>>scores are NOT exactly the same.
>>
>>2. What do you get if you do an equality between the two cells (e.g.
>>in some cell enter the formula =S19=S20 assuming S19 and S20 are the
>>two cells where you have these 19 point scores). If you get FALSE,
>>then they are not the same and the RANK function would rank them
>>differently.
>>
>
> Yes, the two 19-point scores are in S19 and S20. Entering =S19=S20 in a cell
> returns TRUE. <chuckle> What now?


=S19=S20 proves nothing. Try =(S19-S20) instead.

Excel (and almost all other software) follows the IEEE standard for
double precision storage of floating point numbers. Most decimal
fractions cannot be represented exactly with a terminating binary
fraction (just as 1/3 cannot be represented exactly with a terminating
decimal fraction). The net result is that calculated numbers that you
would expect to be the same may not be exactly the same. Excel tries to
help by including a fuzz factor in some operations, thus if A1 contains
0.3-0.2 and A2 contains 0.1, the cell contents will look the same, even
if formatted to 15 decimal places. Because of Excel's fuzz factor,
=A1-A2 will return 0 and =A1=A2 will return TRUE. But =(A1-A2) will
show the very small difference that correctly occurs between the binary
values, and RANK() will distinguish them.

ROUND() should solve the problem, as has already been suggested.

Jerry

http://www.excelforum.com/excel-form...ent-ranks.html

I made the calculation surrounded by a round function and it fixed the problem.

[JBeaucaire]

Excellent.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.