Unique Rank formula not always returning unique ranks

[tcc52]

Hello, I am using Excel 2007. I am using the unique rank formula =rank(value,range) + countif(range,criteria) - 1 to give me unique ranks for a set of dollar values. I have two values of .011 that excel will not give me a unique rank (and they are exactly .011 each - they have the same exact value). But, It is correctly calculating when I have duplicate zeroes.
I know that it is because Excel's simple rank is for some reason giving me different ranks for each of the two .011s(this is the output I'm getting):

# Rank Formula
0 18 =RANK(A15,$A$2:$A$38)
0 18 =RANK(A16,$A$2:$A$38)
0 18 =RANK(A17,$A$2:$A$38)
0 18 =RANK(A18,$A$2:$A$38)
0 18 =RANK(A19,$A$2:$A$38)
0 18 =RANK(A20,$A$2:$A$38)
0 18 =RANK(A21,$A$2:$A$38)
0.005 17 =RANK(A22,$A$2:$A$38)
0.011 16 =RANK(A23,$A$2:$A$38)
0.011 15 =RANK(A24,$A$2:$A$38)

If I copy + paste these value (as values) Excel still gives me the results above.

But:

If I manually type the values in, Excel's simple rank will correctly return duplicate ranks for the two .011s (this is the output I need):

# Rank Formula
0 6 =RANK(K18,$K$18:$K$26)
0 6 =RANK(K19,$K$18:$K$26)
0 6 =RANK(K20,$K$18:$K$26)
0 6 =RANK(K21,$K$18:$K$26)
0.005 5 =RANK(K22,$K$18:$K$26)
0.011 3 =RANK(K23,$K$18:$K$26)
0.011 3 =RANK(K24,$K$18:$K$26)
0.014 2 =RANK(K25,$K$18:$K$26)
0.018 1 =RANK(K26,$K$18:$K$26)

The values are originally the results of a 5-criteria sumifs function that is automated, so they are not something that I can just type in every week, they need to remain automated. My question is...why will excel's simple rank give the duplicate ranks for the zeroes, but not for the two .011s? But then Excel will give me the duplicate rank for the .011s when I manually type them in, why is this?

I appreciate any help!

Tyler

 

[Peter_SSs]

Welcome to the MrExcel board!

Excel does not always calculate exact results. Refer: http://support.microsoft.com/kb/78113

Although in your original data you appear to have two 0.011 values, the underlying numbers may be, for example ..

0.01100012
0.010999

When shown to 3 decimal places, they appear the same, but the RANK formula knows that they are different so gives each one a separate rank.

Try changing your original formulas to something like
=ROUND(original_formula,3)

 

[tcc52]

Hello Peter,

Thanks for the quick reply! Yes, that is what I thought at first, but those two are in fact exactly the same. Here is a sample of my data:

83.82000000000020000
62.35100000000010000
38.80300000000000000
37.78299999999990000
0.01800000000000000
0.01400000000000000
0.01100000000000000
0.01100000000000000
0.00500000000000000
0.00000000000000000
0.00000000000000000
0.00000000000000000
0.00000000000000000
0.00000000000000000

For those two .011s in the middle, excel is not giving me the duplicate ranking that I need; however, it is giving me the duplicate ranking for all of those zeroes at the bottom.

Furthermore, if I manually type those exact #'s in, I will correctly receive the duplicate rank for the .011s.

 

[Peter_SSs]

If those values are in say A10:A23, what happens if you use a vacant column with this in row 10, copied down, then rank these new values?

=ROUND(A10,3)

 

[Peter_SSs]

Further information/examples. In the sheet below, look at the formulas in A1 and A2. If you evaluate them manually, what is inside the parentheses evaluates to zero so when you multiply by 0.01 the result is still zero. A1 and A2 (formatted to show 3 decimal places) appear to reflect that.

However, the RANK formulas in column B do not rank them equally.

Column C formulas just draw the same values from column A but these cells are formatted to 17 decimal places, similar to what your values in post #3 are showing. C1 and C2 still appear equal.

Column D draws the numbers from column A again but this time the cells are formatted to show 25 decimal places. NOW, you can see why the rankings in column B were not equal.

Column E rounds the column A values as I suggested in my last post and the rankings of those numbers in column F are what you would expect.

Column G is just showing how the rounded value can be obtained directly per this from post #2, with the ranking of these numbers again what you would expect in column H.

Try changing your original formulas to something like
=ROUND(original_formula,3)

Formulas in A1, A2, G1 and G2 are stand-alone formulas.
Formulas in B1:F1 and H1 are just copied down to row 2.

Note that there is nothing special about rounding to 3 decimal places, we could just as easily have rounded to 10 decimal places in this example. You just need to round so that you do so before the slight variations start appearing.

Excel Workbook
	A	B	C	D	E	F	G	H
1	0.000	2	0.00000000000000000	0.0000000000000000005551115	0.000	1	0.000	1
2	0.000	1	0.00000000000000000	0.0000000000000000008326673	0.000	1	0.000	1
Rank

 

[tcc52]

Peter, this worked perfectly. Now the simple rank is giving me those duplicate values I needed, which in turn allows the unique ranking formula to give me a unique rank.

Thank you sir!

Tyler