Find Second largest value in an Maxifs function

[Tomjoh92]

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!

 

[Scott T]

Something like this

Excel 2010
	A	B	C	D	E
1	name	number		beth
2	Rick	1		99	largest
3	Bob	2		8	second largest
4	Beth	99		6	thrid
5	Bart	4		1	forth
6	Jim	5
7	Beth	6
8	Rick	7
9	Beth	8
10	Beth	1
Sheet1

Cell Formulas
Range	Formula
D2	{=LARGE(IF($D$1=$A$2:$A$10,$B$2:$B$10),1)}
D3	{=LARGE(IF($D$1=$A$2:$A$10,$B$2:$B$10),2)}
D4	{=LARGE(IF($D$1=$A$2:$A$10,$B$2:$B$10),3)}
D5	{=LARGE(IF($D$1=$A$2:$A$10,$B$2:$B$10),4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tomjoh92]

[TABLE="width: 359"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] J
1 Sort.Nr[/TD]
[TD] K
Navn[/TD]
[TD] L
Volum[/TD]
[/TR]
[TR]
[TD]2 140[/TD]
[TD]0[/TD]
[TD]301[/TD]
[/TR]
[TR]
[TD]3 240[/TD]
[TD]0[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]4 102[/TD]
[TD]0[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]5 200[/TD]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

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]

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..:

Excel 2013/2016
	J	K	L	M	N
1	Sort.Nr	Navn	Volum
2	140	0	301		150
3	240	0	150
4	102	0	200
5	200	0	50
Sheet1

Cell Formulas
Range	Formula
N2	=AGGREGATE(14,6,(L2:L5)/(1-ISNUMBER(MATCH(J2:J5,{100,101,102,103,104,105,106,107,108,109,110,197,200,202,297},0))),2)

 

[Scott T]

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.

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

 

[Tomjoh92]

Thanks a lot!!

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

 

[FormR]

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]

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!