| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula returns error; abbreviating the formula | Excel Discussion (Misc queries) | |||
| SeriesCollection(2).Formula returns formula for series 1 | Excel Programming | |||
| Formula Not Found - Urgent! | Excel Programming | |||
| Lookup returns message box when an exact match is not found | Excel Worksheet Functions | |||
| Question about what the Find function returns if the item wasn't found | Excel Programming | |||
Linear Mode
