Exclude Errors From Small function

[Tikey]

I am using the small function over a range of cells, but sometimes this range includes the #NUM! error, which then creates the same error in my answer. How can I write my function to ignore this error.

2
3
5
6
#NUM!	#NUM!
5
9
7
4

<colgroup><col><col></colgroup><tbody>
</tbody>

Thank you

 

[BarryL]

=SMALL(IF(ISERROR(A:A),"",A:A),1)

If the column in question was A. This is an Array so needs to be entered with ctrl+shft+enter

 

[AliGW]

Wrap the formula in an error trap:

=IFERROR(your_formula_here,"")

 

[Marcelo Branco]

Or you can try the function AGGREGATE with 6 (Ignore Errors) as second argument). Something like
=AGGREGATE(15,6,A1:A10,1)

M.

 

[Tikey]

Thanks for your quick response and help.

Dave