Ranking numbers that are close together with the SAME RANK?

[Guest]

30.12
30.13
30.50
30.75
30.89
30.90
31.00
32.00

Say I had the list of numbers above. I would like to find a fairly easy way
to rank that list like this.

1) 30.12
1) 30.13
3) 30.50
4) 30.75
5) 30.89
5) 30.90
7) 31.00
8) 32.00


30.12 and 30.13 are so close together I would like to give them the same
rank. Same with 30.89 and 30.90. Is there an easy way to do this? Also I
would like to be able to vary the closeness. In this example I gave anything
within .01 the same rank. But I would like to be able to vary that,
anywhere from .01 all the way up to 2.0 or whatever. Thanks very much for
any help you can give me with this.

Peter Gundrum
Milwaukee

 

[Ardus Petus]

With a staging row inserted above data and a staging column (B) beside data
with formula:
=IF(A2-A1<=$E$1,A1,A2)

See example: http://cjoint.com/?fjqWglx47b

HTH

 

[bplumhoff]

Hello Peter,

If your data is in cells A1:A8 then enter in cell B1:
=ROUND(A1/$D$1,0)*$D$1

In cell C1
=RANK(B1,$B$1:$B$8,TRUE)

And in D1:
0.1

Copy B1:C1 down to row 8.

D1 is your "accuracy" cell to which accuracy the original data is
rounded to. Change it to 0.05, for example, then 30.13 will be ranked 2
but 30.89 and 30.90 still are of rank 5.

Works also with unsorted data.

HTH,
Bernd

 

[Guest]

Ardus, one problem I see with your reply is , what if I have 3 or 4 numbers
that are close together? In my example, what if I changed 30.75 to 30.88?

Then I would have 3 numbers (30.88, 30.89, 30.90) that I would want to give
the same rank.

Don't think your answer would work for a situation like that?

Thanks
Peter Gundrum
Milwaukee

 

[Guest]

Hi, bplumhoff, Just like Ardus's reply, your answer does not work when
more than 2 numbers are close together. For example if I have 3 numbers
(30.88, 30.89, 30.90) and the signifigance is .02.

Basically, I want Excel to look at the entire list of numbers and say OK,
these 3 numbers are within .02 or each other so I will give them the same
rank. Or these 4 numbers are within .05 of each other and I will give them
the same rank.

That's what I'm looking for.

Thanks
Peter Gundrum
Milwaukee

 

[bplumhoff]

Hello Peter,

Then try:
B1 (enter as array formula with CTRL + SHIFT + ENTER):
=IF(SUMPRODUCT(--(ABS($A$1:$A$8-A1)<=$D$1))=1,A1,INDEX($A$1:$A$8,MATCH(1,--(ABS($A$1:$A$8-A1)<$D$1),FALSE)))

C1 (enter normal again):
=RANK(B1,$B$1:$B$8,TRUE)

D1:
0.02

and copy B1:C1 down again.

BTW: My former suggestion works if your numbers are rounded to the same
result - no matter how many numbers are rounded to the same. You just
have to find the "right" rounding accuracy (in your counterexample it
should be 0.026, I think).

Regards,
Bernd