Small/Large formula

[MOB]

If I have data such as this;

25
26
32
25
27
31

If I use the formula =small(A1:A6,2) it returns 25

Is there a formula I can use that would return 26 instead? ie second smallest unique number.

TIA

 

[martindwilson]

=MIN(IF($A$1:$A$6>MIN($A$1:$A$6),$A$1:$A$6)) array entered
or
=SMALL( IF( MATCH(A1:A7, A1:A7, 0) = ROW(A1:A7) - MIN(ROW(A1:A7) ) + 1, A1:A7, ""), 2) again array entered change red 2 for 3rd value etc.
credit to shg an nbvc at excel forum for these

 

[Canapone]

Sorry Martin, no mean to overlap.

Regards

 

[MOB]

=SMALL( IF( MATCH(A1:A7, A1:A7, 0) = ROW(A1:A7) - MIN(ROW(A1:A7) ) + 1, A1:A7, ""), 2) again array entered change red 2 for 3rd value etc.
credit to shg an nbvc at excel forum for these

This works great thanks.

If the list looked like this instead (ie a blank cell), I get #N/A. Can I get round this somehow?

25
26
32

25
27
31

 

[Canapone]

Hi,

a possibility (same formula from Martindwilson)

=IF(COUNTBLANK(A1:A7)>0,SMALL(IF(MATCH(A1:A7+0,A1:A7+0,0)=ROW(A1:A7)-MIN(ROW(A1:A7) )+1,A1:A7,""),2+1),SMALL(IF(MATCH(A1:A7,A1:A7,0)=ROW(A1:A7)-MIN(ROW(A1:A7) )+1,A1:A7,""),2))

Hope it helps

Edit: sorry I've forgotten a ";" in the formula from my Italian settings

 

[MOB]

Hi,

a possibility (same formula from Martindwilson)

=IF(COUNTBLANK(A1:A7)>0,SMALL(IF(MATCH(A1:A7+0,A1:A7+0,0)=ROW(A1:A7)-MIN(ROW(A1:A7) )+1,A1:A7,""),2+1),SMALL(IF(MATCH(A1:A7,A1:A7,0)=ROW(A1:A7)-MIN(ROW(A1:A7) )+1,A1:A7,""),2))

Hope it helps

Edit: sorry I've forgotten a ";" in the formula from my Italian settings

Hi

I get #VALUE! with that even entered as an array

Where does the ; need to go?

 

[MOB]

I've just figured out why...........as well as blank cells, there are also cells with "-" that should be ignored.

Can this be done, or should I replace the "-" with zero or blanks?

TIA

 

[Canapone]

Hi Mob, I'm not sure I'm able to help you. The "-"s are produced by a formula? Can they be substituted by zeros?

Meanwhile I hope you'll get better solutions.

Cheers

 

[Robert Mika]

Does this help:

25	26
26
32
27
31
25

<TBODY>
</TBODY>

B1:
=SMALL(A1:A8,COUNTIF(A1:A8,MIN(A1:A8))+1)

 

[martindwilson]

doh so simple!