SMALL/LARGE with IF

M

MarcelXLS

New Member
Joined
Dec 30, 2016
Messages
3
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?

ADEFENSIVE9%
BDEFENSIVE15%
CCYCLICAL8%
DDEFENSIVE12%
EDEFENSIVE10%
FCYCLICAL5%
GCYCLICAL7%
HDEFENSIVE5%
ICYCLICAL6%
JCYCLICAL9%

<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!! ;)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
MarcelXLS said:
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?

ADEFENSIVE9%
BDEFENSIVE15%
CCYCLICAL8%
DDEFENSIVE12%
EDEFENSIVE10%
FCYCLICAL5%
GCYCLICAL7%
HDEFENSIVE5%
ICYCLICAL6%
JCYCLICAL9%

<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!! ;)
Click to expand...
DEFENSIVECYCLICALDEFENSIVECYCLICAL
ADEFENSIVE9%10.090.150.09
BDEFENSIVE15%20.150.120.08
CCYCLICAL8%30.080.10.07
DDEFENSIVE12%40.120.090.06
EDEFENSIVE10%50.10.050.05
FCYCLICAL5%60.05#NUM!#NUM!
GCYCLICAL7%70.07#NUM!#NUM!
HDEFENSIVE5%80.05#NUM!#NUM!
ICYCLICAL6%90.06#NUM!#NUM!
JCYCLICAL9%100.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>
 
Upvote 0
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.
 
Upvote 0
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? :rolleyes:
 
Upvote 0
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 ))


HIJKL
2​
RankDefensive%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>
 
Last edited:
Upvote 0
FranzV said:
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 ))


HIJKL
2​
RankDefensive%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>
Click to expand...

Many thanks FranzV, it works! And I should have asked you hours ago!! Have a great day!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Incremental count of unique values from a dynamically generated list.
Replies
6
Views
337
vampsthevampyre
V
F
  • Question
Tie-Breaking Question (Using Index/Match/Large)
Replies
3
Views
325
Fluff
Fluff
S
  • Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
104
strugglingaccountant
S
O
  • Question
Trying to create a standby list
Replies
8
Views
345
Fluff
Fluff
R
  • Question
Applying a formula to a subset of rows
Replies
3
Views
144
rodqntr
R

Forum statistics

Threads
1,214,832
Messages
6,121,849
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top