Thread: Returning 2nd smallest value in a range
View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_] Ashish Mathur[_2_] is offline
external usenet poster
  
Posts: 1,766
Default Returning 2nd smallest value in a range
Hi Rachel,

Suppose the range of numbers is as follows in range D5:D11

1
1
0

5
6
1

In cell E5, enter the following formula
=IF(OR(COUNTIF($D$5:$D5,D5)<1,COUNTBLANK(D5)=1),1 000,1) and copy down till
E11

Now you can array enter (Ctrl+Shift+Enter) the following formula
=SMALL(IF(($E$5:$E$11=1),D5:D11),2)


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rachel7" wrote in message
...
I'm using the SMALL formula to return 2nd smallest value in a range of
cells. Is there a way of the formula excluding empty cells, zero value
cells
& cells with same values in?
Thanks in advance

Reply With QuoteReply With Quote