SMALL/LARGE with IF

[MarcelXLS]

Hello, I am an Excel beginner and was experimenting for hours with LARGE with a condition IF. Unfortunately, I did not make it..can anyone be of help please?

A	DEFENSIVE	9%
B	DEFENSIVE	15%
C	CYCLICAL	8%
D	DEFENSIVE	12%
E	DEFENSIVE	10%
F	CYCLICAL	5%
G	CYCLICAL	7%
H	DEFENSIVE	5%
I	CYCLICAL	6%
J	CYCLICAL	9%

<tbody>
</tbody>

I would like to rank the A, B, C etc. according to their size (10%, etc.). One ranking for the DEFENSIVE and one for the CYCLICAL. Any assistance? Thank you so much and...HAPPY NEW YEAR!!

 

[oldbrewer]

Hello, I am an Excel beginner and was experimenting for hours with LARGE with a condition IF. Unfortunately, I did not make it..can anyone be of help please?

A	DEFENSIVE	9%
B	DEFENSIVE	15%
C	CYCLICAL	8%
D	DEFENSIVE	12%
E	DEFENSIVE	10%
F	CYCLICAL	5%
G	CYCLICAL	7%
H	DEFENSIVE	5%
I	CYCLICAL	6%
J	CYCLICAL	9%

<tbody>
</tbody>

I would like to rank the A, B, C etc. according to their size (10%, etc.). One ranking for the DEFENSIVE and one for the CYCLICAL. Any assistance? Thank you so much and...HAPPY NEW YEAR!!

					DEFENSIVE	CYCLICAL		DEFENSIVE	CYCLICAL
A	DEFENSIVE	9%		1	0.09			0.15	0.09
B	DEFENSIVE	15%		2	0.15			0.12	0.08
C	CYCLICAL	8%		3		0.08		0.1	0.07
D	DEFENSIVE	12%		4	0.12			0.09	0.06
E	DEFENSIVE	10%		5	0.1			0.05	0.05
F	CYCLICAL	5%		6		0.05		#NUM!	#NUM!
G	CYCLICAL	7%		7		0.07		#NUM!	#NUM!
H	DEFENSIVE	5%		8	0.05			#NUM!	#NUM!
I	CYCLICAL	6%		9		0.06		#NUM!	#NUM!
J	CYCLICAL	9%		10		0.09		#NUM!	#NUM!
			can you not hide helper columns like these to get your desired output ?

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[FranzV]

You could also do it in a single column with a COUNTIFS function that will check for rows that have the same Class and a higher value (asuming that #1 is the one with the highest %). I also included an Overall Rank that you can make with the RANK.EQ function but accepts no conditions natively.

[B]Excel 2016 (Windows) 64 bit[/B][TABLE]
<colgroup><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]2[/TD]
[TD]Item[/TD]
[TD]Class[/TD]
[TD]Value[/TD]
[TD]ClassRank[/TD]
[TD]OverallRank[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]A[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]B[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]C[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]D[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]E[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]F[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]G[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]H[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]I[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]J[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Item[/TD]
[TD]Class[/TD]
[TD]Value[/TD]
[TD]ClassRank[/TD]
[TD]OverallRank[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]J[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]C[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]G[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]I[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]F[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]B[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]D[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]E[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]A[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]H[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Hoja1[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD]=COUNTIFS([COLOR=#0000FF]$C$3:$C$12,$C3,$D$3:$D$12,">="&$D3[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD]=RANK.EQ([COLOR=#0000FF]$D3,$D$3:$D$12,0[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I hope it helps.

 

[MarcelXLS]

Thank you Oldbrewer. Well, I would like to have something like a leaderbord, e.g. in one table the defensive names descending by size (row 1 = B at 15%, row 2 = D at 12%, etc.). And in another table the same for the cyclical names. Is that possible without five days of coding?

 

[FranzV]

You could add the following formulas to create your ranking tables:

On cells I3 to I7 you can copy the following array formula and press CTRL+SHIFT+ENTER to turn add the curly brackets at both ends and make it work.

{=INDEX($B$3:$B$12,MATCH($H3:$H7,$E$3:$E$12*($C$3:$C$12=I$2),0))}

​

<tbody>
</tbody>

On cells J3 to J7 you can use this normal formula.

=INDEX($D$3:$D$12, MATCH( I3, $B$3:$B$12, 0 ))

	H	I	J	K	L
2​	Rank	Defensive	%	Cyclical	%
3​	1​	B	15%​	J	9%​
4​	2​	D	12%​	C	8%​
5​	3​	E	10%​	G	7%​
6​	4​	A	9%​	I	6%​
7​	5​	H	5%​	F	5%​

<tbody>
</tbody>

 

[MarcelXLS]

You could add the following formulas to create your ranking tables:

On cells I3 to I7 you can copy the following array formula and press CTRL+SHIFT+ENTER to turn add the curly brackets at both ends and make it work.

{=INDEX($B$3:$B$12,MATCH($H3:$H7,$E$3:$E$12*($C$3:$C$12=I$2),0))}

​

<tbody>
</tbody>

On cells J3 to J7 you can use this normal formula.

=INDEX($D$3:$D$12, MATCH( I3, $B$3:$B$12, 0 ))

	H	I	J	K	L
2​	Rank	Defensive	%	Cyclical	%
3​	1​	B	15%​	J	9%​
4​	2​	D	12%​	C	8%​
5​	3​	E	10%​	G	7%​
6​	4​	A	9%​	I	6%​
7​	5​	H	5%​	F	5%​

<tbody>
</tbody>

Many thanks FranzV, it works! And I should have asked you hours ago!! Have a great day!