Ranking Absolute Deviation from Targeted Value

[rkeen0822]

I am trying to build a ranking database based on percentage values where 100% is the target and would be ranked the further away from the goal the ranking ascends.

Example below is keyed in values as an example

Performance Value	Ranked Value
86%	5
91%	3
101%	1
74%	6
110%	4
95%	2
59%	7

Looking to do this in a singular row, without the need for additional columns. If possible, Grouped Rankins within 90%-100%, would like to group as "Target Met" so would be ranked as a 1.

 

[Eric W]

How about:

Book1
	A	B	C	D	E
1	Performance Value			Performance Value
2	86%	5		86%	5
3	91%	3		91%	1
4	101%	1		101%	1
5	74%	6		74%	6
6	110%	4		110%	1
7	95%	2		95%	1
8	59%	7		59%	7
Sheet8

Cell Formulas
Range	Formula
B2:B8	B2	=MATCH(ABS(1-A2:A8),SORT(ABS(1-A2:A8)))
E2:E8	E2	=IF(D2:D8>=0.9,1,MATCH(ABS(1-D2:D8),SORT(ABS(1-D2:D8))))
Dynamic array formulas.

Also, please update your profile to show what version of Excel you're using. It can affect what functions are available to solve your question. Also, do you have to worry about ties in your data?

 

[rkeen0822]

thank you, and will update