| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If statement for a range of cells
Hi, I want to know if it is possible to create an IF statement that can for example give a TRUE value if all the cells in the range A1 to C50 are greater or equal to 0 and FALSE otherwise.
I have tried IF((A1:C50)=0,TRUE,FALSE), but it does not work. Thanks for your help, Daniel |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If statement for a range of cells
wrote in message
... Hi, I want to know if it is possible to create an IF statement that can for example give a TRUE value if all the cells in the range A1 to C50 are greater or equal to 0 and FALSE otherwise. I have tried IF((A1:C50)=0,TRUE,FALSE), but it does not work. Thanks for your help, Daniel |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If statement for a range of cells
[Sorry about the previous premature fat-finger send.]
wrote: Hi, I want to know if it is possible to create an IF statement that can for example give a TRUE value if all the cells in the range A1 to C50 are greater or equal to 0 and FALSE otherwise. I have tried IF((A1:C50)=0,TRUE,FALSE), but it does not work. You might try one of the following array-entered formulas (press ctrl+shift+Enter instead of just Enter): =IF(AND(A1:C50=0),TRUE) or =AND(A1:C50=0) Alternatively, try the following normally-entered formula (just press Enter): =IF(COUNTIF(A1:C50,"=0")=COUNT(A1:C50),TRUE) or =COUNTIF(A1:A50,"=0")=COUNT(A1:C50) Caveat: COUNTIF treats numeric text differently from COUNT. And A1:C50=0 treats all text as TRUE. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If statement for a range of cells
Thank you, it worked perfectly.
On Monday, March 4, 2013 6:37:31 PM UTC-5, joeu2004 wrote: [Sorry about the previous premature fat-finger send.] wrote: Hi, I want to know if it is possible to create an IF statement that can for example give a TRUE value if all the cells in the range A1 to C50 are greater or equal to 0 and FALSE otherwise. I have tried IF((A1:C50)=0,TRUE,FALSE), but it does not work. You might try one of the following array-entered formulas (press ctrl+shift+Enter instead of just Enter): =IF(AND(A1:C50=0),TRUE) or =AND(A1:C50=0) Alternatively, try the following normally-entered formula (just press Enter): =IF(COUNTIF(A1:C50,"=0")=COUNT(A1:C50),TRUE) or =COUNTIF(A1:A50,"=0")=COUNT(A1:C50) Caveat: COUNTIF treats numeric text differently from COUNT. And A1:C50=0 treats all text as TRUE. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change colors in a range of excel cells based on another range of cells | Excel Programming | |||
| IF statement on a range of cells in Excel 2007 | Excel Worksheet Functions | |||
| IF Statement for Cells in a Range | Excel Discussion (Misc queries) | |||
| Using variables as first and last cells in range statement | Excel Worksheet Functions | |||
| Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement | Excel Programming | |||
Linear Mode
