Rank a Range based around the number #1

[cawsax1]

Hello and good day,

I have a range of data with numbers between +0.10 and +3.51. The central tendency I want to be closest to is 100% or 1.

I want to rank the range as follows

1) Rank All Numbers above and below 1 (closest # to 1 = rank 1)

e.g.
1.00 = rank 1 variance = 0.00
0.99 = rank 2 variance = 0.01
1.08 = rank 3 variance = 0.08
1.09 = rank 4 variance = 0.09
0.90 = rank 5 variance = 0.10
1.11 = rank 6 variance = 0.11
1.12 = rank 7 variance = 0.12

I have about 2404 records I would like to rank utilizing this method.

I have tried this formula.

=IF(OY16>1,RANK([OY16],$OY$16:$OY$2419)-COUNTIF($OY$16:$OY$2419,">1"),RANK([OY16],$OY$16:$OY$2419,1))

This results in the highest value 3.51 being ranked 1

Thanks for any help

 

[AlanY]

try

Excel 2012
	A	B	C
1	2.82	1.82	18
2	2.28	1.28	11
3	1.11	0.11	3
4	3.08	2.08	19
5	0.81	0.19	5
6	0.24	0.76	9
7	2.3	1.3	12
8	2.73	1.73	16
9	1.51	0.51	8
10	0.93	0.07	2
11	0.7	0.3	7
12	1.16	0.16	4
13	0.99	0.01	1
14	2.67	1.67	15
15	2.37	1.37	13
16	1.19	0.19	5
17	3.47	2.47	20
18	2.09	1.09	10
19	2.75	1.75	17
20	2.49	1.49	14
Sheet2

Cell Formulas
Range	Formula
B1	=ABS(A1-1)
C1	=RANK(B1,$B$1:$B$20,1)

 

[cawsax1]

Thank you very much, you made quick work this problem. I think that will work great. I appreciate your help!

 

[AlanY]

you're welcome