Ranking Groups with Multiple Criteria

[mwicks]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A

[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]GROUP
[/TD]
[TD]TARGET
[/TD]
[TD]ACTUAL
[/TD]
[TD]% ATTAINMENT
[/TD]
[TD]EXPECTED RANK
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]9
[/TD]
[TD]17
[/TD]
[TD]188.9%
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]7
[/TD]
[TD]13
[/TD]
[TD]185.7%
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]11
[/TD]
[TD]183.3%
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]12
[/TD]
[TD]18
[/TD]
[TD]150%
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]9
[/TD]
[TD]150%
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1
[/TD]
[TD]11
[/TD]
[TD]1100%
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]3
[/TD]
[TD]9
[/TD]
[TD]300%
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]4
[/TD]
[TD]8
[/TD]
[TD]200%
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]200%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[TD]100%
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]100%
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[TD]100%
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]24
[/TD]
[TD]20
[/TD]
[TD]83.3%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]31
[/TD]
[TD]6
[/TD]
[TD]19.4%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]14
[/TD]
[TD]19
[/TD]
[TD]135.7%
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]34
[/TD]
[TD]37
[/TD]
[TD]108.8%
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]24
[/TD]
[TD]24
[/TD]
[TD]100%
[/TD]
[TD]3

[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]26
[/TD]
[TD]13
[/TD]
[TD]50%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello, I would like to rank a set of data by group and based on multiple criteria. I've given a sample below with the expected output in column E. For each group, I would like to rank by % Attainment (column D), and break ties with Actual (column C). Also, I would like the ranking to skip any line where the % Attainment is less than 100%, or where the Actual is less than 3. Any help would be greatly appreciated!

 

[Peter_SSs]

Try this, copied down.

Excel Workbook
	A	B	C	D	E
1	GROUP	TARGET	ACTUAL	% ATTAINMENT	EXPECTED RANK
2	A	9	17	188.90%	1
3	A	7	13	185.70%	2
4	A	6	11	183.30%	3
5	A	12	18	150%	4
6	A	6	9	150%	5
7	B	1	11	1100%	1
8	B	3	9	300%	2
9	B	4	8	200%	3
10	B	1	2	200%
11	B	6	6	100%	4
12	C	7	7	100%	1
13	C	6	6	100%	2
14	C	24	20	83.30%
15	C	31	6	19.40%
16	D	14	19	135.70%	1
17	D	34	37	108.80%	2
18	D	24	24	100%	3
19	D	26	13	50%
Sheet1

 

[mwicks]

That worked perfectly! Thank you very much Peter!!

 

[Peter_SSs]

That worked perfectly! Thank you very much Peter!!

You're very welcome. Thanks for the follow-up.