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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
O

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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
F

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
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
M

MarcelXLS

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

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
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
M

MarcelXLS

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

C
  • Question
Conditional "Running Sum" Ranking
Replies
9
Views
373
ajitexcel
A
N
  • Question
Formula for a single list of Dates from two payment cycles and a list of fix dates
Replies
0
Views
662
narendra
N
joerenaud
  • Solved
Help with Golf League Points Calculation
Replies
8
Views
237
Fluff
Fluff
I
  • Question
showing 2 labels from 2 different data set in X-axis (horizonal)
Replies
0
Views
103
INN
I
O
  • Question
Macro to calculate grouped investments values based on selected month
Replies
0
Views
238
OET1982
O

Forum statistics

Threads
1,191,708
Messages
5,988,234
Members
440,139
Latest member
ngaicuong2017

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
Top