Small/Large formula

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
=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
 
Last edited:

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows
=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

Active Member
Joined
May 10, 2007
Messages
463

ADVERTISEMENT

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
 
Last edited:

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,031
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
May 10, 2007
Messages
463
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Does this help:
25
26
26
32
27
31
25

<TBODY>
</TBODY>

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

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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
Top