If you had two cells containing negative numbers, then the third
smallest may be zero but the overall criteria is not met. I tried this
variation as an array formula (CSE):

=IF(SUM(IF(SMALL(range_test,{1,2,3})=0,1))=3,0,"no ")

but it fails if there is at least one negative number. This amendment
(also CSE) seems to work:

=IF(SUM(IF(SMALL(range_test,{1,2,3,4,5})=0,1))=3, 0,"no")

but it falls down if any cell is empty.

I like Sandy's solution <bg

Hope this helps.

Pete

On Nov 29, 12:49 am, "Gary" wrote:
Thanks Sandy.

Wouldnt the following formula do what I want?

=IF(SMALL(namedrange,3)=0,0

I am not sure.

"Sandy Mann" wrote in message

...



Do empty cells count as zero? If so then:

=IF(SUM((A4=0),(C4=0),(E4=0),(G4=0),(I4=0))2,0,"" )

If you want to exclude counting empty cells as zeros then:

=IF(SUM((A4=0)*(A4<""),(C4=0)*(C4<""),(E4=0)*(E4 <""),(G4=0)*(G4<""),(I4-=0)*(I4<""))2,0,"")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Gaurav" wrote in message
...
What can be the shortest formula for this?

If any 3 of 5 non adjacent cells have 0 then 0.

Thanks- Hide quoted text -

- Show quoted text -