Rank Function and Number that are close together

  • Thread starter Peter Gundrum Milwaukee WI
  • Start date
P

Peter Gundrum Milwaukee WI

30.57
30.56
30.98
30.23
30.17
30.00
30.16

Say I wanted to Rank these numbers from lowest to highest. Easy enough, I
just use the rank function. My question is. For the numbers that are
close together like 30.56 and 30.57 or 30.16 and 30.17, is there any way
to give these numbers the same RANK?

Can I tell Excel somehow to give numbers say within .01 of each other the
same RANK some how?

Any input would be appreciated. If you want to email me it's
[email protected]

Basically I would like the above list ranked like this.

1)30.00
2)30.16
2)30.17
4)30.23
5)30.56
5)30.57
7)30.98


But I would also like to be able to vary the closeness. In this example say
anywhere from .01 to maybe .10.


Any help is appreciated. Thanks.
 
M

Miguel Zapico

One approach can be using a "help" column, with the significance you want
(using ROUND, CEILING or FLOOR functions) and rank that column.
You can craft later another column with that rank and the original values.
 
Top