Formula returns #value! if value is not found

[Arno]

Hello,

Formula returns #value! if value is not found:

=SMALL(V14:V21,IF(COUNTIF(V14:V21,"<0"),1,COUNTIF(V14:V21,0)+1))

Is there a solution to avoid #value! I need only to see the minimum

Thanks Arno

 

[Mike H]

Hi,

I don't see how you het a #Value! error out of this, you would get #NUM!.
You can achieve that with your own formula like this

=IF(ISERROR(SMALL(V14:V21,IF(COUNTIF(V14:V21,"<0"),1,COUNTIF(V14:V21,0)+1))),"",SMALL(V14:V21,IF(COUNTIF(V14:V21,"<0"),1,COUNTIF(V14:V21,0)+1)))


But I'd go with the more compact array formula which does the same thing

=IF(ISERROR(SMALL(IF(V14:V21<>0,V14:V21),1)),"",SMALL(IF(V14:V21<>0,V14:V21),1))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Arno]

Thank you ! Ciao Arno

Hi,

I don't see how you het a #Value! error out of this, you would get #NUM!.
You can achieve that with your own formula like this

=IF(ISERROR(SMALL(V14:V21,IF(COUNTIF(V14:V21,"<0"),1,COUNTIF(V14:V21,0)+1))),"",SMALL(V14:V21,IF(COUNTIF(V14:V21,"<0"),1,COUNTIF(V14:V21,0)+1)))


But I'd go with the more compact array formula which does the same thing

=IF(ISERROR(SMALL(IF(V14:V21<>0,V14:V21),1)),"",SMALL(IF(V14:V21<>0,V14:V21),1))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Mike H]

Your welcome

Thank you ! Ciao Arno