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

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Largest[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Smallest[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]2nd Largest[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]2nd Smallest[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"]500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]600[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]600[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]-800[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"]-800[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] "][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]-8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-8[/TD]
[TD="align: right"]-8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]-9[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]

</tbody>

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

 

[DanteAmor]

Why P9 = 50 (Should it be 80?)
Why Q9 = 10 (Should it be -9?)

Why Q8 = -8 (Should it be ""?)

 

[hsandeep]

Why P9 = 50 (Should it be 80?)P9 should be 50 & not 80 since non duplicate is required
Why Q9 = 10 (Should it be -9?)Q9 should be 10 & not -9 since non duplicate is required
Why Q8 = -8 (Should it be ""?) Q8 should be -8 & not “” since 2n dlargest (<>), <>””)

 

[Akuini]

Hi, hsandeep
I might have an idea, using UDF. Is that ok with you?

 

[jasonb75]

This looks like it works, note that the fomulas in P3 and Q3 must be array confirmed with Ctrl Shift Enter.

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)),""))

 

[hsandeep]

This looks like it works, note that the fomulas in P3 and Q3 must be array confirmed with Ctrl Shift Enter.

the 4 formulas are not working..
largest: For B3=500 & C3:L3=600; N3 should be 600 (formula gives answer as "");
smallest: For B3=500 & C3:L3=600; O3 should be 500 (formula gives answer as "")
Even 2nd largest & 2nd smallest are not working

 

[jasonb75]

The formulas work fine, the sample table that you provided is not consistent with your description of the problem.

In the example that you proved in post 1, B3:L3 =0, B5 = 500 & C5:L5 = 600

With the above, all 4 results in N3:Q3 = "" because there are no non 0 values in the row.
N5 = 600, O5 = 500, P5 = "", Q5 = "" which is exactly what you say it should be.

If you want a better answer, ask a better question.

 

[DanteAmor]

[Delete

 

[DanteAmor]

Why P9 = 50 (Should it be 80?)P9 should be 50 & not 80 since non duplicate is required
Why Q9 = 10 (Should it be -9?)Q9 should be 10 & not -9 since non duplicate is required
Why Q8 = -8 (Should it be ""?) Q8 should be -8 & not “” since 2n dlargest (<>), <>””)

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

 

[jasonb75]

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