Handling #NUM! error

  • Thread starter Thread starter Michel Khennafi
  • Start date Start date
M

Michel Khennafi

Good afternoon gurus,

I created a formula in a spreadsheet that returns the followin g error #NUM!

I have 4 zero values in C662:F662 and the formula I created in G662 is
=SMALL(C662:F662,COUNTIF(C662:F662,0)+1)

How could I modify the formula to avoid the #NUM! error display... I tried
the formula if(erro.type... in G662 but got no success!

Help is more than welcome

Be well

Thanks
 
Michel Khennafi said:
I have 4 zero values in C662:F662 and the formula I created in G662 is
=SMALL(C662:F662,COUNTIF(C662:F662,0)+1)

How could I modify the formula to avoid the #NUM! error display...
Click to expand...

If the formula above returns #NUM! because there are only 4 cells in
this range, they're all zero, so the COUNTIF call returns 4, so the
2nd argument to SMALL is 5, and 5 exceeds the number of cells in
C662:F662. If you want the smallest positive number in that range or
"" if there aren't any, try

=IF(COUNTIF(C662:F662,">0"),SMALL(C662:F662,COUNTIF(C662:F662,"<=0")
+1),
"")
 
Back
Top