A minor thing first, the unary minus (the double -ve) is redundant in the
previous example.

This amended array entered formula should return an error (#NUM) if there
are no duplicate non-zero numbers

=SMALL(IF((COUNTIF(ref,ref)1)*ref,ref),1)

IOW if the resultant array is all non-numeric values, ie all FALSE's, Small
fails which I think is what you want (could include an additional IFERROR
etc).

but can it be made to return
the second smallest (non zero) number is there are no repeating ones to
return the lowest of?

I'm sure it's possible, but...?

If this snippet of the above formula -
(COUNTIF(ref,ref)1)*ref
is applied to this array
{0;0;1;1;1;2;7;7}
returns
{0;0;1;1;1;0;7;7}

Would need to return the second + 1 (third) samllest unique, in the above 7.

Might be worth asking over in excel.worksheetfunctions.

Re speed, normally cell formulas are faster but these formulas are doing a
lot more work than say the UDF posted by RBS. However if as you say you only
have 15 values to process the formula should be faster due to the overhead
of even the simplest UDF.

Regards,
Peter T

"Riddler" wrote in message
ups.com...
I like the simplicity of the array formula but can it be made to return
the second smallest (non zero) number is there are no repeating ones to
return the lowest of?
I played with it a bit but did not have any luck. I need to learn more
about array formulas. It looks like they have some great potential. As
for speed of them my array of numbers is usually less than 15 numbers
so it runs plenty fast enough.

Thanks
Scott