Find Second largest value in an Maxifs function

Tomjoh92

New Member
Joined
Jan 24, 2018
Messages
4
Hi!

I need some help. I want to find the second largest value in an MAXIFS function. My Maxifs function only returns the highest.

I've tried to use the large function around the maxifs but it didnt work. Any suggestions?

Thanks for helping!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Something like this

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">name</td><td style=";">number</td><td style="text-align: right;;"></td><td style=";">beth</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Rick</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">99</td><td style=";">largest</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Bob</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style=";">second largest</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Beth</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style=";">thrid </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Bart</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">forth</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Jim</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Beth</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Rick</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Beth</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Beth</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">$D$1=$A$2:$A$10,$B$2:$B$10</font>),1</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">$D$1=$A$2:$A$10,$B$2:$B$10</font>),2</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">$D$1=$A$2:$A$10,$B$2:$B$10</font>),3</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">$D$1=$A$2:$A$10,$B$2:$B$10</font>),4</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Tomjoh92

New Member
Joined
Jan 24, 2018
Messages
4
J
1
Sort.Nr
K
Navn
L
Volum
2 1400301
3 2400150
4 1020200
5 200050

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Here is the data simplified.
I want to find the second highest value in colume L when colume J not contains 100,101,102,103,104,105,106,107,108,109,110,197,200,202,297.

In dataset above, i want the output to be 150, since J4 is 102.


I really appreciate all the help. Been cracking my brains out over this.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
I want to find the second highest value in colume L when colume J not contains 100,101,102,103,104,105,106,107,108,109,110,197,200,202,297.

Hi, welcome to the forum!

How about something like this..:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Sort.Nr</td><td style=";">Navn</td><td style=";">Volum</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">140</td><td style="text-align: right;;">0</td><td style="text-align: right;;">301</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">150</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">240</td><td style="text-align: right;;">0</td><td style="text-align: right;;">150</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">102</td><td style="text-align: right;;">0</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">200</td><td style="text-align: right;;">0</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">14,6,(<font color="Red">L2:L5</font>)/(<font color="Red">1-ISNUMBER(<font color="Green">MATCH(<font color="Purple">J2:J5,{100,101,102,103,104,105,106,107,108,109,110,197,200,202,297},0</font>)</font>)</font>),2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Just to add to what FormR posted if you want the largest, 2nd largest, 3rd largest... then you can use rows for the k value so that it increments as you drag it down.

Code:
=IFERROR(AGGREGATE(14,6,($L$2:$L$5)/(1-ISNUMBER(MATCH($J$2:$J$5,$Q$2:$Q$16,0))),ROWS(H$2:H2)),"")
 
Last edited:

Tomjoh92

New Member
Joined
Jan 24, 2018
Messages
4
Thanks a lot!!

This worked fine. Had to make a list with the numbers since the {} syntax didnt work in Norwegian Excel.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Great, glad it worked :)

Had to make a list with the numbers since the {} syntax didnt work in Norwegian Excel.

It's arguably better to list the numbers on the sheet anyway but if you really wanted to use the array constant you could try one of these instead.

{100;101;102;103;104;105;106;107;108;109;110;197;200;202;297}
Or
{100\101\102\103\104\105\106\107\108\109\110\197\200\202\297}
 

Tomjoh92

New Member
Joined
Jan 24, 2018
Messages
4
Great, glad it worked :)



It's arguably better to list the numbers on the sheet anyway but if you really wanted to use the array constant you could try one of these instead.

{100;101;102;103;104;105;106;107;108;109;110;197;200;202;297}
Or
{100\101\102\103\104\105\106\107\108\109\110\197\200\202\297}


Thank you!
Second alternative worked!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,413
Messages
5,595,998
Members
414,037
Latest member
Roamingsmile

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