| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if any 3 of 5 cells have 0 then 0.
What can be the shortest formula for this?
If any 3 of 5 non adjacent cells have 0 then 0. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
Plus the OP states that the cells are non adjacent
-- Regards, Peo Sjoblom "Pete_UK" wrote in message ... 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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
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,"") -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Plus the OP states that the cells are non adjacent -- Regards, Peo Sjoblom "Pete_UK" wrote in message ... 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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
Peo,
I set up the named range "range_test" to cover five non-adjacent cells. Pete On Nov 29, 1:51 am, "Peo Sjoblom" wrote: Plus the OP states that the cells are non adjacent -- Regards, Peo Sjoblom "Pete_UK" wrote in message ... 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 -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
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) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Peo, I set up the named range "range_test" to cover five non-adjacent cells. Pete On Nov 29, 1:51 am, "Peo Sjoblom" wrote: Plus the OP states that the cells are non adjacent -- Regards, Peo Sjoblom "Pete_UK" wrote in message ... 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 -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
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 On Nov 29, 6:06 pm, "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Peo, I set up the named range "range_test" to cover five non-adjacent cells. Pete On Nov 29, 1:51 am, "Peo Sjoblom" wrote: Plus the OP states that the cells are non adjacent -- Regards, Peo Sjoblom "Pete_UK" wrote in message ... 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if any 3 of 5 cells have 0 then 0.
Thanks a ton everyone.
"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
| display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
| Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
| conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
| trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
![Gaurav[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
