largest, smallest, 2nd largest & 2nd smallest

[hsandeep]

Posted on https://www.excelforum.com/excel-ge...d-largest-and-2nd-smallest-2.html#post5221789

but could not got complete answer

I need 4 formulae for: largest, smallest, 2nd largest (smaller than largest & non duplicate of largest) & 2nd smallest (larger than smallest & non duplicate of smallest) from B3:L3 (contiguous columns) in N3, O3, P3, Q3

Conditions #1 : If all values in B3:L3 are zero 0 or null, then all 4 formulae should give answers as blank.
Conditions #2 : largest & 2nd largest cannot be same, similarly smallest & 2nd smallest cannot be same.

Example: If B3=500, C3=600, D3=600, E3=600, F3=600, G3=600, H3=600, I3=600, J3=600, K3=600, L3=600 then largest=600, smallest=500, 2^nd largest=blank, 2^nd smallest=blank

Conditions #3 : Any of the 4 answers cannot be zero. This condition #3 requires more clarifications which are:

Example: If B3=0, C3=800, D3=-800, E3=-20, F3=-9, G3=-2, H3=-1, I3=0, J3=0, K3=0, L3=0 then largest=800, smallest=-800, 2^nd largest=-1, 2^nd smallest=-20

Conditions #4 : If all the values are same (but <>0 or <>””), then Example: If B3=-5, C3=-5, D3=-5, E3=-5, F3=-5, G3=-5, H3=-5, I3=-5, J3=-5, K3=-5, L3=-5 then largest=-5, smallest=-5, 2^nd largest=blank, 2^nd smallest=blank

Note: 2^nd largest & 2^nd smallest can be same

Example: If B3=0, C3=800, D3=-8, E3=0, F3=-9, G3=0, H3=0, I3=0, J3=0, K3=0, L3=0 then largest=800, smallest=-9, 2^nd largest=-8, 2^nd smallest=-8
How to accomplish?
Thanks

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2													Largest	Smallest	2nd Largest	2nd Smallest
3	0	0	0	0	0	0	0	0	0	0	0
4
5	500	600	600	600	600	600	600	600	600	600	600		600	500	600	600
6	0	800	-800	-20	-9	-2	-1	0	0	0	0		800	-800	-1	-20
7	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5
8	0	800	-8	0	-9	0	0	0	0	0	0		800	-9	-8	-8
9	80	32	10	20	30	10	50	0	-9	80	-9		80	-9	50	10
10	800	800	0	0	0	0	0	0	0	0	0		800	800

<tbody>
</tbody>

Correct answer for P5="",Q5=""
Correct answer for N7=-5,O7=-5

 

[hsandeep]

Answer in rows 5, 8 and 10 you have duplicate values. you can explain?

Answer in rows 5 you have duplicate values You are correct. Correct answer is: N5=600, O5=500, P5=””, Q5=”” (largest & 2nd largest cannot be same, similarly smallest & 2nd smallest cannot be same, Condition #2 in the original post #1 )

Answer in rows 8 you have duplicate values I am correct. -8 in 2^nd largest & -8 in 2^nd smallest is not duplicate values. (2^nd largest & 2^nd smallest can be same, Note in the original post #1 )

Answer in rows 10 you have duplicate values I am correct. 800 in largest & 800 in smallest is not duplicate values. (largest & smallest can be same)

Please try to understand:

I am ‘grabbing’ largest & 2^nd largest; smallest & 2nd smallest. largest & 2^nd largest cannot be same, similarly, smallest & 2nd smallest cannot be same.

All 4 answers must be <>0 (they can be null if nothing is grabbed in any of the 4)

 

[hsandeep]

sample data:correctly modified:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2													Largest	Smallest	2nd Largest	2nd Smallest
3	0	0	0	0	0	0	0	0	0	0	0
4
5	500	600	600	600	600	600	600	600	600	600	600		600	500
6	0	800	-800	-20	-9	-2	-1	0	0	0	0		800	-800	-1	-20
7	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5		-5	-5
8	0	800	-8	0	-9	0	0	0	0	0	0		800	-9	-8	-8
9	80	32	10	20	30	10	50	0	-9	80	-9		80	-9	50	10
10	800	800	0	0	0	0	0	0	0	0	0		800	800

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 34px"><col style="WIDTH: 15px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 77px"><col style="WIDTH: 86px"></colgroup><tbody>
</tbody>

 

[Akuini]

Hi, hsandeep
jasonb75 formula gives the same result as your result in post 12. So what's the problem?

 

[hsandeep]

Hi, hsandeep
jasonb75 formula gives the same result as your result in post 12. So what's the problem?

Hi Akuini
Which formula is giving correct results?
Consider row #5 : N5 should be 600 (formula gives ""). Is it giving 600? If yes, what is the formula?
O5 should be 500 (formula gives ""). Is it giving 500? If yes, what is the formula?

 

[jasonb75]

Try reading all replies not just one, then you might find the answer before asking the question. There is a link in post 10 to a file with your sample data and the working formulas in place for you to see.

 

[hsandeep]

I am very thankful to you for helping me to get the solution with your ideas / formulas / presentation. The help offered in it is a huge obligation. jasonb75, I am going to read the link, which I could not do so due to some work….& thereafter would certainly report you back Sir.

 

[hsandeep]

Try reading all replies not just one, then you might find the answer before asking the question. There is a link in post 10 to a file with your sample data and the working formulas in place for you to see.

Yes this is the correct answers which I am looking for the 4 formulas. The sheet was read only...........I am trying to figure out the formulas in the cells, jasonb75

 

[hsandeep]

Formulas from post 5 working https://1drv.ms/x/s!At_kSiUZ5yAXsFeSop5fSroVmPHD?e=OwCNtx

Answers are correct on onedrive....but when I put the formulas in Excel Workbook, I am getting blanks in all cells. Definitely some mistake I am doing....but unable to solve. Please help. Attached
Sheet1

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2													Largest	Smallest	2nd Largest	2nd Smallest
3	0	0	0	0	0	0	0	0	0	0	0
4
5	500	600	600	600	600	600	600	600	600	600	600
6	0	800	-800	-20	-9	-2	-1	0	0	0	0
7	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5	-5
8	0	800	-8	0	-9	0	0	0	0	0	0
9	80	32	10	20	30	10	50	0	-9	80	-9
10	800	800	0	0	0	0	0	0	0	0	0

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 8px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"><col style="WIDTH: 35px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula N3 =IFERROR(AGGREGATE(14,6,1/(1/B3:L3),1),"") O3 =IFERROR(AGGREGATE(15,6,1/(1/B3:L3),1),"") P3 {=IF(SUM(IFERROR(1/COUNTIF(B3:L31,1/(1/(B3:L3))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B3:L3),1+COUNTIF(B3:L3,N3)),""))} Q3 {=IF(SUM(IFERROR(1/COUNTIF(B3:L31,1/(1/(B3:L3))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B3:L3),1+COUNTIF(B3:L3,N3)),""))} N4 =IFERROR(AGGREGATE(14,6,1/(1/B4:L4),1),"") O4 =IFERROR(AGGREGATE(15,6,1/(1/B4:L4),1),"") P4 {=IF(SUM(IFERROR(1/COUNTIF(B4:L32,1/(1/(B4:L4))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B4:L4),1+COUNTIF(B4:L4,N4)),""))} Q4 {=IF(SUM(IFERROR(1/COUNTIF(B4:L32,1/(1/(B4:L4))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B4:L4),1+COUNTIF(B4:L4,N4)),""))} N5 =IFERROR(AGGREGATE(14,6,1/(1/B5:L5),1),"") O5 =IFERROR(AGGREGATE(15,6,1/(1/B5:L5),1),"") P5 {=IF(SUM(IFERROR(1/COUNTIF(B5:L33,1/(1/(B5:L5))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B5:L5),1+COUNTIF(B5:L5,N5)),""))} Q5 {=IF(SUM(IFERROR(1/COUNTIF(B5:L33,1/(1/(B5:L5))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B5:L5),1+COUNTIF(B5:L5,N5)),""))} N6 =IFERROR(AGGREGATE(14,6,1/(1/B6:L6),1),"") O6 =IFERROR(AGGREGATE(15,6,1/(1/B6:L6),1),"") P6 {=IF(SUM(IFERROR(1/COUNTIF(B6:L34,1/(1/(B6:L6))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B6:L6),1+COUNTIF(B6:L6,N6)),""))} Q6 {=IF(SUM(IFERROR(1/COUNTIF(B6:L34,1/(1/(B6:L6))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B6:L6),1+COUNTIF(B6:L6,N6)),""))} N7 =IFERROR(AGGREGATE(14,6,1/(1/B7:L7),1),"") O7 =IFERROR(AGGREGATE(15,6,1/(1/B7:L7),1),"") P7 {=IF(SUM(IFERROR(1/COUNTIF(B7:L35,1/(1/(B7:L7))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B7:L7),1+COUNTIF(B7:L7,N7)),""))} Q7 {=IF(SUM(IFERROR(1/COUNTIF(B7:L35,1/(1/(B7:L7))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B7:L7),1+COUNTIF(B7:L7,N7)),""))} N8 =IFERROR(AGGREGATE(14,6,1/(1/B8:L8),1),"") O8 =IFERROR(AGGREGATE(15,6,1/(1/B8:L8),1),"") P8 {=IF(SUM(IFERROR(1/COUNTIF(B8:L36,1/(1/(B8:L8))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B8:L8),1+COUNTIF(B8:L8,N8)),""))} Q8 {=IF(SUM(IFERROR(1/COUNTIF(B8:L36,1/(1/(B8:L8))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B8:L8),1+COUNTIF(B8:L8,N8)),""))} N9 =IFERROR(AGGREGATE(14,6,1/(1/B9:L9),1),"") O9 =IFERROR(AGGREGATE(15,6,1/(1/B9:L9),1),"") P9 {=IF(SUM(IFERROR(1/COUNTIF(B9:L37,1/(1/(B9:L9))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B9:L9),1+COUNTIF(B9:L9,N9)),""))} Q9 {=IF(SUM(IFERROR(1/COUNTIF(B9:L37,1/(1/(B9:L9))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B9:L9),1+COUNTIF(B9:L9,N9)),""))} N10 =IFERROR(AGGREGATE(14,6,1/(1/B10:L10),1),"") O10 =IFERROR(AGGREGATE(15,6,1/(1/B10:L10),1),"") P10 {=IF(SUM(IFERROR(1/COUNTIF(B10:L38,1/(1/(B10:L10))),0))=2,"",IFERROR(AGGREGATE(14,6,1/(1/B10:L10),1+COUNTIF(B10:L10,N10)),""))} Q10 {=IF(SUM(IFERROR(1/COUNTIF(B10:L38,1/(1/(B10:L10))),0))=2,"",IFERROR(AGGREGATE(15,6,1/(1/B10:L10),1+COUNTIF(B10:L10,N10)),""))} <tbody> </tbody>

Formula Array:

<tbody>
</tbody>

 

[jasonb75]

The file is read only in your browser, but you should be able to edit it if you download it.

The only reason that I can think of for the results to be blank is that you're using excel 2007, with newer versions the formula would work fine, with older versions it would show a #NAME ? error in every cell.
If this is correct, then the alternative formulas below should work for you.

Note that all of these must be array confirmed with Ctrl Shift Enter.

N5 =IFERROR(LARGE(IFERROR(1/(1/B3:L3),""),1),"")
O5 =IFERROR(SMALL(IFERROR(1/(1/B3:L3),""),1),"")
P5 =IF(SUM(IFERROR(1/COUNTIF(B3:L31,1/(1/(B3:L3))),0))=2,"",IFERROR(LARGE(IFERROR(1/(1/B3:L3),""),1+COUNTIF(B3:L3,N3)),""))
Q5 =IF(SUM(IFERROR(1/COUNTIF(B3:L31,1/(1/(B3:L3))),0))=2,"",IFERROR(SMALL(IFERROR(1/(1/B3:L3),""),1+COUNTIF(B3:L3,N3)),""))

Hopefully that will work, I'm running out of ideas.

 

[hsandeep]

Hopefully that will work

jasonb75, appreciate you for getting this solution so quickly so that I was able to implement it properly. Appreciate the time you took to share your experience and insights.
I changed 31 to 3 in the formula’s cell address.