Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default If any 3 of 5 values are 0 then 0.

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.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default If any 3 of 5 values are 0 then 0.

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

"Gary" wrote:

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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default If any 3 of 5 values are 0 then 0.

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


"Gary" wrote:

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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default If any 3 of 5 values are 0 then 0.

Make sure press Ctrl+Shift+Enter, not just enter in my early replied


"Teethless mama" wrote:

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


"Gary" wrote:

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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default If any 3 of 5 values are 0 then 0.

=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)

"Gary" wrote in message
...
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.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default If any 3 of 5 values are 0 then 0.

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)

"Teethless mama" wrote in message
...
=IF(OR((MOD(G7:O7,2)=1)*(COUNTIF(G7:O7,0)=3),O7=0 ),0,SUM(G7:O7))


"Gary" wrote:

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.





  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default If any 3 of 5 values are 0 then 0.

Thanks Everyone

"Gary" wrote in message
...
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.



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default If any 3 of 5 values are 0 then 0.

On Oct 31, 9:12 am, "Gary" wrote:
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.


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!

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default If any 3 of 5 values are 0 then 0.

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,O1 7<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.


On Oct 31, 9:12 am, "Gary" wrote:
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding numerical values based on multiple values in another column Kazmaniac Excel Worksheet Functions 6 April 4th 07 08:53 PM
Advanced Filter for Values in Column M greater than Values in Colu SteveC Excel Discussion (Misc queries) 3 May 2nd 06 07:55 PM
Count unique values and create list based on these values vipa2000 Excel Worksheet Functions 7 August 5th 05 01:17 AM
create list of unique values from a column with repeated values? Chad Schaben Excel Worksheet Functions 1 July 8th 05 10:25 PM
Excel Compare values in columns & display missing values in a new cpetta Excel Discussion (Misc queries) 1 April 2nd 05 05:51 AM


All times are GMT +1. The time now is 10:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"