If any 3 of 5 values are 0 then 0.

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0,"") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.
 
Hi,

There's bound to be a better way but this works if there are no negative
values:-


=IF(SMALL(Myrange,3)=0,0,SUM(Myrange))

I've used a named range 'Myrange' for your 5 cells.

Mike
 
=IF(OR(O7=0,SUMPRODUCT(--(MOD(COLUMN(G7:O7),2)=1),--(G7:O7=0))>=3),0,
SUMPRODUCT(--(MOD(COLUMN(G7:O7),2)=1),G7:O7))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Should you be MODing the column, and summing the MOD columns in case any
others have values

=IF(OR((MOD(COLUMN(G7:O7),2)=1)*(COUNTIF(G7:O7,0)>=3),O7=0),0,SUM(IF(MOD(COLUMN(G7:O7),2)=1,G7:O7)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi,

I can do it with IF(OR(AND formula but i will have to write all the
combinations of 3 out of these 5 values. for example...

=IF(OR(AND(O7=0,M7=0,K7=0),AND(O7=0,M7=0,G7=0)),0,"") something like this.

Is there a smarter way of saying if any 3 of these 5 cells have 0 then
return 0 else return sum of all 5?

these 5 cells are O7, M7, K7, G7, I7.

another condition is that if O7 alone is 0 then also it should return 0.

Regards.
Click to expand...

Try...

=IF(O7=0,0,IF(INDEX(FREQUENCY((G7,I7,K7,M7,O7),{0.999999999999999,0}),
2)>=3,0,SUM(G7,I7,K7,M7,O7)))

Hope this helps!
 
I don't know how much better this is than what's already been
suggested, but you can try:

=--AND(((O17=0)+(M17=0)+(K17=0)+(I17=0)+(G17=0))<3,O17<>0)

It returns one if neither 0 condition is met, so you can multiply it
by another value to get a different result. For instance, say that
formula is in A1, you can then say in A2:

=AVERAGE(B2:B10)*A1

and that will return the average if neither 017 is 0 nor more than 2
others are 0. Otherwise, it will show 0.
 
Back
Top