largest, smallest, 2nd largest & 2nd smallest

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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, 2nd largest=blank, 2nd 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, 2nd largest=-1, 2nd 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, 2nd largest=blank, 2nd smallest=blank

Note: 2nd largest & 2nd 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, 2nd largest=-8, 2nd smallest=-8
How to accomplish?
Thanks

BCDEFGHIJKLMNOPQ

<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
 
Last edited:
Sort by date Sort by votes
DanteAmor said:
Answer in rows 5, 8 and 10 you have duplicate values. you can explain?
Click to expand...
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 2nd largest & -8 in 2nd smallest is not duplicate values. (2nd largest & 2nd 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 & 2nd largest; smallest & 2nd smallest. largest & 2nd 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)
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
sample data:correctly modified:


BCDEFGHIJKLMNOPQ

<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>
[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=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="align: right"]-5[/TD]
[TD="align: right"]-5[/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>
 
Upvote 0
Hi, hsandeep
jasonb75 formula gives the same result as your result in post 12. So what's the problem?
 
Upvote 0
Akuini said:
Hi, hsandeep
jasonb75 formula gives the same result as your result in post 12. So what's the problem?
Click to expand...
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
jasonb75 said:
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.
Click to expand...
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
 
Upvote 0
jasonb75 said:
Formulas from post 5 working https://1drv.ms/x/s!At_kSiUZ5yAXsFeSop5fSroVmPHD?e=OwCNtx
Click to expand...

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

BCDEFGHIJKLMNOPQ
LargestSmallest2nd Largest2nd Smallest

<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>
[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=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="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="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=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="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="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]

</tbody>

Spreadsheet Formulas
CellFormula
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>
 
Upvote 0
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.
 
Upvote 0
jasonb75 said:
Hopefully that will work
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
What if investment
Replies
2
Views
61
tigerzen
T
K
  • Solved
Average Based on Criteria
Replies
3
Views
378
Fluff
Fluff
K
  • Question Question
Populate With Criteria
Replies
3
Views
68
Peter_SSs
Peter_SSs
HappyChappy
  • Solved
tidying up formulas
Replies
4
Views
328
HappyChappy
HappyChappy
HappyChappy
  • Solved
Converting time problem
Replies
2
Views
304
HappyChappy
HappyChappy

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

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
Back
Top