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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
71
tigerzen
T
K
  • Solved
Average Based on Criteria
Replies
3
Views
388
Fluff
Fluff
K
  • Question Question
Populate With Criteria
Replies
3
Views
84
Peter_SSs
Peter_SSs
B
  • Question Question
Update List Number Going Forward in a Column Once Other Cell Is Greater Than A Specific $ Number
2
Replies
13
Views
211
bear1970
B
A
  • Question Question
Find tool is very slow
Replies
2
Views
42
alex_345
A

Forum statistics

Threads
1,224,749
Messages
6,180,725
Members
452,995
Latest member
isldboy

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