if any 3 of 5 cells have 0 then 0.

[Gaurav]

What can be the shortest formula for this?

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

Thanks

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gary]

Thanks Sandy.

Wouldnt the following formula do what I want?

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

I am not sure.

 

[Pete_UK]

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

 

[Peo Sjoblom]

Plus the OP states that the cells are non adjacent


--

Regards,

Peo Sjoblom

 

[T. Valko]

If there will not be any negative numbers, this also excludes empty cells as
being =0.

=IF(FREQUENCY((A1,C2,E3,G4,I5),0)=3,0,"")

 

[Pete_UK]

Peo,

I set up the named range "range_test" to cover five non-adjacent
cells.

Pete

 

[T. Valko]

SMALL/LARGE will take multiple reference areas:

=SMALL((A1:A5,A8,C2:C4,D7,J1),1)
=LARGE((A1:A5,A8,C2:C4,D7,J1),1)

 

[Pete_UK]

I didn't know that, Biff, so thanks for letting me know. Drawback is,
though, that if the cells change then the formulae have to be changed,
but using a named range means you only have to change that.

Pete

 

[Gaurav]

Thanks a ton everyone.