Formula returns #value! if value is not found
 

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

Formula returns #value! if value is not found
 

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(COU NTIF(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)),"",SM ALL(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" wrote:

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

Formula returns #value! if value is not found
 

Thank you ! Ciao Arno

"Mike H" wrote:

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(COU NTIF(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)),"",SM ALL(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" wrote:

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

Formula returns #value! if value is not found
 

Your welcome

"Arno" wrote:

Thank you ! Ciao Arno

"Mike H" wrote:

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(COU NTIF(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)),"",SM ALL(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" wrote:

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