| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if any value in a range is equal to 0
Hi all.
Quick question. I have several cells - i.e M8 : M12. These cells can contain integers 0 to 10. I want to be able to design a formula that says - if any cell in the range M8:M12 contains a 0 then change the value of M13 to say "INVALID", otherwise it is "VALID". I'm having a bit of trouble designing this - tried nest IF statements and formula. Suggestions appreciated. Regards Daz |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if any value in a range is equal to 0
=IF(COUNTIF(M8:M12,0), "Invalid", "Valid")
"Daz" wrote: Hi all. Quick question. I have several cells - i.e M8 : M12. These cells can contain integers 0 to 10. I want to be able to design a formula that says - if any cell in the range M8:M12 contains a 0 then change the value of M13 to say "INVALID", otherwise it is "VALID". I'm having a bit of trouble designing this - tried nest IF statements and formula. Suggestions appreciated. Regards Daz |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if any value in a range is equal to 0
This should work
=IF(OR(M8=0,M9=0,M10=0,M11=0,M12=0),"Invalid","Val id") Chad Daz wrote: Hi all. Quick question. I have several cells - i.e M8 : M12. These cells can contain integers 0 to 10. I want to be able to design a formula that says - if any cell in the range M8:M12 contains a 0 then change the value of M13 to say "INVALID", otherwise it is "VALID". I'm having a bit of trouble designing this - tried nest IF statements and formula. Suggestions appreciated. Regards Daz |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if any value in a range is equal to 0
If you are open to a suggestion, I think you could shorten it to
=IF(OR(M8:M12=0),"Invalid","Valid") array entered with Cntrl+Shift+Enter (or you get #VALUE). "Chad" wrote: This should work =IF(OR(M8=0,M9=0,M10=0,M11=0,M12=0),"Invalid","Val id") Chad Daz wrote: Hi all. Quick question. I have several cells - i.e M8 : M12. These cells can contain integers 0 to 10. I want to be able to design a formula that says - if any cell in the range M8:M12 contains a 0 then change the value of M13 to say "INVALID", otherwise it is "VALID". I'm having a bit of trouble designing this - tried nest IF statements and formula. Suggestions appreciated. Regards Daz |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if any value in a range is equal to 0
Thanks for these very helpful suggestions.
JMB wrote: If you are open to a suggestion, I think you could shorten it to =IF(OR(M8:M12=0),"Invalid","Valid") array entered with Cntrl+Shift+Enter (or you get #VALUE). "Chad" wrote: This should work =IF(OR(M8=0,M9=0,M10=0,M11=0,M12=0),"Invalid","Val id") Chad Daz wrote: Hi all. Quick question. I have several cells - i.e M8 : M12. These cells can contain integers 0 to 10. I want to be able to design a formula that says - if any cell in the range M8:M12 contains a 0 then change the value of M13 to say "INVALID", otherwise it is "VALID". I'm having a bit of trouble designing this - tried nest IF statements and formula. Suggestions appreciated. Regards Daz |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to check for a date range match in one column and then count thevalues equal to in another | Excel Worksheet Functions | |||
| to check if the values in two sheets are equal | Excel Discussion (Misc queries) | |||
| VBA check to see if variable value is odd but not equal to 1 | Excel Discussion (Misc queries) | |||
| check if 2 cells are equal but only if they contain numbers not i. | Excel Worksheet Functions | |||
| check if 2 cells are equal but only if they contain numbers not i. | Excel Worksheet Functions | |||
Linear Mode
