Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |