#NUM! being returned instead of 0 or blank
 

I was told to use the formula below which allows the lowest number within a
range to be displayed discounting any zeros, this works fine.

=SMALL(D51:H51,COUNTIF(D51:H51,"<=0")+1)

The new problem that I am now having is that this is reliant upon a value
being present with which to calculate otherwise "#NUM!" is being returned. Is
there a way in which I can get the around this and have the returned value a
0 or just blank? The subsequent retuned value is then referenced into a
column but because "#NUM!" is displayed the column will not add together.

Thank you
--
Yorkie

#NUM! being returned instead of 0 or blank
 

Add a check to the beginning of your function. Enter the following with
CTRL+SHIFT+ENTER now, as it is an array function:

=IF(AND(D51:H51=""),"",SMALL(D51:H51,COUNTIF(D51:H 51,"<=0")+1))

--
Regards,
Dave


"The Countryman" wrote:

I was told to use the formula below which allows the lowest number within a
range to be displayed discounting any zeros, this works fine.

=SMALL(D51:H51,COUNTIF(D51:H51,"<=0")+1)

The new problem that I am now having is that this is reliant upon a value
being present with which to calculate otherwise "#NUM!" is being returned. Is
there a way in which I can get the around this and have the returned value a
0 or just blank? The subsequent retuned value is then referenced into a
column but because "#NUM!" is displayed the column will not add together.

Thank you
--
Yorkie

#NUM! being returned instead of 0 or blank
 

David Billigmeier wrote...
Add a check to the beginning of your function. Enter the following with
CTRL+SHIFT+ENTER now, as it is an array function:

=IF(AND(D51:H51=""),"",SMALL(D51:H51,COUNTIF(D51: H51,"<=0")+1))
....

Could still fubar if there were text in the range but no numbers. SMALL
(and LARGE) return #NUM! errors when there are fewer numbers in the
range or array given as first argument than the positive integer given
as second argument, so the proper error trap should be ensuring there's
at least one positive number in the range. Myself, I'd use

=IF(COUNTIF(rng,"0"),LARGE(rng,COUNTIF(rng,"0")) ,"")