Changing the Ranking on a List if Criteria(s) are Met or Not Met

[Dan Bosvac]

I'm working on a spreadsheet with three columns: A) Party List (1-10), B) Initials of 10 Persons C) Percent Votes Received. The criteria to sort the data is 1) Persons receiving more than 10% of votes move to the top positions of the list 2) Persons receiving less than 10% retain their positions on the list, except if they have been bumped by the person receiving more than 10%. So for example, if I have the following data (Position/Initials/%Vote): 1/DV/2.12, 2/IP/16.26, 3/ZL/1.72, 4/BL/11.86, 5/JM/2.78, 6/TH/1.68, 7/KB/19.28, 8/MP/0.58, 9/GB/5.3, 10/DD/7.46. Based on the two criteria, the new position of the persons on the list should be: 1/KB/19.28, 2/IP/16.26, 3/BL/11.86, 4/DV/2.12, 5/ZL/1.72, 6/JM/2.78, 7/TH/1.68, 8/MP/0.58, 9/GB/5.3, 10/DD/7.46.

I have an additional 9 columns after the "percent votes received", changing the original "percent votes received" values by multiplying by (-1) if a value over 10% was used. I then tested the formulas below in each of the 10 columns:

=INDEX(B2:B15,MATCH(MAX(E2:E15),E2:E15,0),1) - This formula will rank the positions and pick out the initials associated with that rank BUT DOES NOT TAKE INTO ACCOUNT that if the greater than 10% criteria IS NOT MET, the position DOES NOT CHANGE with respect to the original list.

=IF(C2>10,(INDEX(B2:B15,MATCH(MAX(C2:C15),C2:C15,0),1)),INDEX(B2:B15,SORT(A2:A15,1))) - This is the closest I've come to figuring this out BUT I need somehow to modify the formula to check that all of the "percent votes received" values in column c2:c10 meet the > 10%criteria. What happens here is that if C2>10, it returns the initials of that person who received greater than 10%. If C2<10, it returns all of the initials in the original list.

I'm hoping you can provide some suggestions moving forward. Thanks for your consideration.

 

[Eric W]

Welcome to MrExcel!

I'm not sure what the extra 9 columns are for, unless they're just working columns in an attempt to find the formula. But try this:

Book1
	A	B	C	D	E	F	G	H
1	Party List	Initials	Percent Votes Received			Party List	Initials	Percent Votes Received
2	1	DV	2.12			7	KB	19.28
3	2	IP	16.26			2	IP	16.26
4	3	ZL	1.72			4	BL	11.86
5	4	BL	11.86			1	DV	2.12
6	5	JM	2.78			3	ZL	1.72
7	6	TH	1.68			5	JM	2.78
8	7	KB	19.28			6	TH	1.68
9	8	MP	0.58			8	MP	0.58
10	9	GB	5.3			9	GB	5.3
11	10	DD	7.46			10	DD	7.46
Sheet3

Cell Formulas
Range	Formula
F2:G11	F2	=INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$11)/($C$2:$C$11=$H2),COUNTIF($H$2:$H2,$H2)))
H2:H11	H2	=IFERROR(AGGREGATE(14,6,$C$2:$C$11/($C$2:$C$11>=10),ROWS($H$2:$H2)),INDEX(C:C,AGGREGATE(15,6,ROW($C$2:$C$11)/($C$2:$C$11<10),ROWS($H$2:$H2)-COUNTIF($C$2:$C$11,">=10"))))

 

[Peter_SSs]

Keeping Eric's suggestion for columns F:G, offering a slightly shorter formula for column H

20 07 11.xlsm
	A	B	C	D	E	F	G	H
1	Party List	Initials	Percent Votes Received			Party List	Initials	Percent Votes Received
2	1	DV	2.12			7	KB	19.28
3	2	IP	16.26			2	IP	16.26
4	3	ZL	1.72			4	BL	11.86
5	4	BL	11.86			1	DV	2.12
6	5	JM	2.78			3	ZL	1.72
7	6	TH	1.68			5	JM	2.78
8	7	KB	19.28			6	TH	1.68
9	8	MP	0.58			8	MP	0.58
10	9	GB	5.3			9	GB	5.3
11	10	DD	7.46			10	DD	7.46
Sort

Cell Formulas
Range	Formula
H2:H11	H2	=IF(ROWS(H$2:H2)>COUNTIF(C$2:C$11,">=10"),INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$11)/(C$2:C$11<10),COUNTIF(H$1:H1,"<10")+1)),LARGE(C$2:C$11,ROWS(H$2:H2)))

 

[Dan Bosvac]

Welcome to MrExcel!

I'm not sure what the extra 9 columns are for, unless they're just working columns in an attempt to find the formula. But try this:

Book1
	A	B	C	D	E	F	G	H
1	Party List	Initials	Percent Votes Received			Party List	Initials	Percent Votes Received
2	1	DV	2.12			7	KB	19.28
3	2	IP	16.26			2	IP	16.26
4	3	ZL	1.72			4	BL	11.86
5	4	BL	11.86			1	DV	2.12
6	5	JM	2.78			3	ZL	1.72
7	6	TH	1.68			5	JM	2.78
8	7	KB	19.28			6	TH	1.68
9	8	MP	0.58			8	MP	0.58
10	9	GB	5.3			9	GB	5.3
11	10	DD	7.46			10	DD	7.46
Sheet3

Cell Formulas
Range	Formula
F2:G11	F2	=INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$11)/($C$2:$C$11=$H2),COUNTIF($H$2:$H2,$H2)))
H2:H11	H2	=IFERROR(AGGREGATE(14,6,$C$2:$C$11/($C$2:$C$11>=10),ROWS($H$2:$H2)),INDEX(C:C,AGGREGATE(15,6,ROW($C$2:$C$11)/($C$2:$C$11<10),ROWS($H$2:$H2)-COUNTIF($C$2:$C$11,">=10"))))

Thanks, much appreciated

 

[Dan Bosvac]

Keeping Eric's suggestion for columns F:G, offering a slightly shorter formula for column H

20 07 11.xlsm
	A	B	C	D	E	F	G	H
1	Party List	Initials	Percent Votes Received			Party List	Initials	Percent Votes Received
2	1	DV	2.12			7	KB	19.28
3	2	IP	16.26			2	IP	16.26
4	3	ZL	1.72			4	BL	11.86
5	4	BL	11.86			1	DV	2.12
6	5	JM	2.78			3	ZL	1.72
7	6	TH	1.68			5	JM	2.78
8	7	KB	19.28			6	TH	1.68
9	8	MP	0.58			8	MP	0.58
10	9	GB	5.3			9	GB	5.3
11	10	DD	7.46			10	DD	7.46
Sort

Cell Formulas
Range	Formula
H2:H11	H2	=IF(ROWS(H$2:H2)>COUNTIF(C$2:C$11,">=10"),INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$11)/(C$2:C$11<10),COUNTIF(H$1:H1,"<10")+1)),LARGE(C$2:C$11,ROWS(H$2:H2)))

Thanks, much appreciated!