Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I guess I need a function/formula for the following: I want cell C38
to show a "Yes" or a "No" based on cell C8, C9, or C10 having a value of 1. It doesn't matter if more than one of these cells has a 1. Therefore, if there is a 1, then I want cell C38 to show 'Yes'; else, 'No'. Also, show 'No' is all of the cells (C8:C10) are blank or have a number other than 1 or any other letters/characters. Also, if it is not too much trouble, would you please give me couple of different solutions. I am using excel 2007. Thanks! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hello,
if you type into cell C38 =IF(OR(C6=1,C7=1,C8=1),"Yes","No") you will get your desired affect -- Kevin Smith :o) "dcb1" wrote: I guess I need a function/formula for the following: I want cell C38 to show a "Yes" or a "No" based on cell C8, C9, or C10 having a value of 1. It doesn't matter if more than one of these cells has a 1. Therefore, if there is a 1, then I want cell C38 to show 'Yes'; else, 'No'. Also, show 'No' is all of the cells (C8:C10) are blank or have a number other than 1 or any other letters/characters. Also, if it is not too much trouble, would you please give me couple of different solutions. I am using excel 2007. Thanks! |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
"dcb1" wrote:
if it is not too much trouble, would you please give me couple of different solutions. I'm not in the habit of giving answers to tests. But what the heck! Normal formulas (commit with Enter as usual): 1. =IF(COUNTIF(C8:C10,1)0,"Yes","No") 2. =IF(SUMIF(C8:C10,1)0,"Yes","No") 3. =IF(SUMPRODUCT(--(C8:C10=1))0,"Yes","No") 4. =IF(OR(C8=1,C9=1,C10=1),"Yes","No") 5. =IF(C8=1,"Yes",IF(C9=1,"Yes",IF(C10=1,"Yes","No")) ) Array formulas (commit with ctrl+shift+Enter, not Enter): 1. =IF(OR(C8:C10=1),"Yes","No") None of those is specific to Excel 2007. ----- original message ----- "dcb1" wrote in message ... I guess I need a function/formula for the following: I want cell C38 to show a "Yes" or a "No" based on cell C8, C9, or C10 having a value of 1. It doesn't matter if more than one of these cells has a 1. Therefore, if there is a 1, then I want cell C38 to show 'Yes'; else, 'No'. Also, show 'No' is all of the cells (C8:C10) are blank or have a number other than 1 or any other letters/characters. Also, if it is not too much trouble, would you please give me couple of different solutions. I am using excel 2007. Thanks! |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks for your help--- it worked!
On Aug 23, 6:51*am, "JoeU2004" wrote: "dcb1" wrote: if it is not too much trouble, would you please give me couple of different solutions. I'm not in the habit of giving answers to tests. *But what the heck! Normal formulas (commit with Enter as usual): 1. =IF(COUNTIF(C8:C10,1)0,"Yes","No") 2. =IF(SUMIF(C8:C10,1)0,"Yes","No") 3. =IF(SUMPRODUCT(--(C8:C10=1))0,"Yes","No") 4. =IF(OR(C8=1,C9=1,C10=1),"Yes","No") 5. =IF(C8=1,"Yes",IF(C9=1,"Yes",IF(C10=1,"Yes","No")) ) Array formulas (commit with ctrl+shift+Enter, not Enter): 1. =IF(OR(C8:C10=1),"Yes","No") None of those is specific to Excel 2007. ----- original message ----- "dcb1" wrote in message ... I guess I need a function/formula for the following: *I want cell C38 to show a "Yes" or a "No" based on cell C8, C9, or C10 having a value of 1. *It doesn't matter if more than one of these cells has a 1. Therefore, if there is a 1, then I want cell C38 to show 'Yes'; else, 'No'. *Also, show 'No' is all of the cells (C8:C10) are blank or have a number other than 1 or any other letters/characters. *Also, if it is not too much trouble, would you please give me couple of different solutions. *I am using excel 2007. *Thanks!- Hide quoted text - - Show quoted text - |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks for responding and showing me the different ways to solve my
problem! While the first 5 ways you gave worked, I know I am doing something wrong when I tried the last one =IF(OR(C8:C10=1),"Yes","No") since it only showed "###" in cell C38. I pressed the ctrl+shift +enter--- not sure how to get this one to work. Again, Thanks for your help! On Aug 23, 6:51*am, "JoeU2004" wrote: "dcb1" wrote: if it is not too much trouble, would you please give me couple of different solutions. I'm not in the habit of giving answers to tests. *But what the heck! Normal formulas (commit with Enter as usual): 1. =IF(COUNTIF(C8:C10,1)0,"Yes","No") 2. =IF(SUMIF(C8:C10,1)0,"Yes","No") 3. =IF(SUMPRODUCT(--(C8:C10=1))0,"Yes","No") 4. =IF(OR(C8=1,C9=1,C10=1),"Yes","No") 5. =IF(C8=1,"Yes",IF(C9=1,"Yes",IF(C10=1,"Yes","No")) ) Array formulas (commit with ctrl+shift+Enter, not Enter): 1. =IF(OR(C8:C10=1),"Yes","No") None of those is specific to Excel 2007. ----- original message ----- "dcb1" wrote in message ... I guess I need a function/formula for the following: *I want cell C38 to show a "Yes" or a "No" based on cell C8, C9, or C10 having a value of 1. *It doesn't matter if more than one of these cells has a 1. Therefore, if there is a 1, then I want cell C38 to show 'Yes'; else, 'No'. *Also, show 'No' is all of the cells (C8:C10) are blank or have a number other than 1 or any other letters/characters. *Also, if it is not too much trouble, would you please give me couple of different solutions. *I am using excel 2007. *Thanks!- Hide quoted text - - Show quoted text - |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Select the cell with that formula in it and then look in the Formula Bar...
are there curly braces like this {} around the formula? If not, then you did something wrong. To correct it, click anywhere within the Formula Bar and press Ctrl+Shift+Enter again (all at the same time) and see if that makes the formula work. -- Rick (MVP - Excel) "dcb1" wrote in message ... Thanks for responding and showing me the different ways to solve my problem! While the first 5 ways you gave worked, I know I am doing something wrong when I tried the last one =IF(OR(C8:C10=1),"Yes","No") since it only showed "###" in cell C38. I pressed the ctrl+shift +enter--- not sure how to get this one to work. Again, Thanks for your help! On Aug 23, 6:51 am, "JoeU2004" wrote: "dcb1" wrote: if it is not too much trouble, would you please give me couple of different solutions. I'm not in the habit of giving answers to tests. But what the heck! Normal formulas (commit with Enter as usual): 1. =IF(COUNTIF(C8:C10,1)0,"Yes","No") 2. =IF(SUMIF(C8:C10,1)0,"Yes","No") 3. =IF(SUMPRODUCT(--(C8:C10=1))0,"Yes","No") 4. =IF(OR(C8=1,C9=1,C10=1),"Yes","No") 5. =IF(C8=1,"Yes",IF(C9=1,"Yes",IF(C10=1,"Yes","No")) ) Array formulas (commit with ctrl+shift+Enter, not Enter): 1. =IF(OR(C8:C10=1),"Yes","No") None of those is specific to Excel 2007. ----- original message ----- "dcb1" wrote in message ... I guess I need a function/formula for the following: I want cell C38 to show a "Yes" or a "No" based on cell C8, C9, or C10 having a value of 1. It doesn't matter if more than one of these cells has a 1. Therefore, if there is a 1, then I want cell C38 to show 'Yes'; else, 'No'. Also, show 'No' is all of the cells (C8:C10) are blank or have a number other than 1 or any other letters/characters. Also, if it is not too much trouble, would you please give me couple of different solutions. I am using excel 2007. Thanks!- Hide quoted text - - Show quoted text - |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
"dcb1" wrote:
when I tried the last one =IF(OR(C8:C10=1),"Yes","No") since it only showed "###" in cell C38. Excel is trying to display an error, e.g. #VALUE, but your column is not wide enough. Widen the column to see the error. I pressed the ctrl+shift+enter Most likely, you did not do that, despite all good intentions. Select the cell, press F2, then press ctrl+shift+Enter. Remember to continue to hold both Ctrl and Shift until you press Enter. But if you did that and you see curly braces around the entire formula in the Formula Bar next to "fx" just under the toolbards, it is also possible that there is an Excel error (#VALUE, #REF, #DIV, etc) in one of the referenced cells, C8:C10. It is best to avoid such errors. But if you need to tolerate the situation, post back for an embellishment to the formulas. PS: The latter scenario -- error propogation -- seems unlikely because it would plague the non-array formulas as well. But you say they work. However, perhaps something changed in the interim. ----- original message ----- "dcb1" wrote in message ... Thanks for responding and showing me the different ways to solve my problem! While the first 5 ways you gave worked, I know I am doing something wrong when I tried the last one =IF(OR(C8:C10=1),"Yes","No") since it only showed "###" in cell C38. I pressed the ctrl+shift +enter--- not sure how to get this one to work. Again, Thanks for your help! On Aug 23, 6:51 am, "JoeU2004" wrote: "dcb1" wrote: if it is not too much trouble, would you please give me couple of different solutions. I'm not in the habit of giving answers to tests. But what the heck! Normal formulas (commit with Enter as usual): 1. =IF(COUNTIF(C8:C10,1)0,"Yes","No") 2. =IF(SUMIF(C8:C10,1)0,"Yes","No") 3. =IF(SUMPRODUCT(--(C8:C10=1))0,"Yes","No") 4. =IF(OR(C8=1,C9=1,C10=1),"Yes","No") 5. =IF(C8=1,"Yes",IF(C9=1,"Yes",IF(C10=1,"Yes","No")) ) Array formulas (commit with ctrl+shift+Enter, not Enter): 1. =IF(OR(C8:C10=1),"Yes","No") None of those is specific to Excel 2007. ----- original message ----- "dcb1" wrote in message ... I guess I need a function/formula for the following: I want cell C38 to show a "Yes" or a "No" based on cell C8, C9, or C10 having a value of 1. It doesn't matter if more than one of these cells has a 1. Therefore, if there is a 1, then I want cell C38 to show 'Yes'; else, 'No'. Also, show 'No' is all of the cells (C8:C10) are blank or have a number other than 1 or any other letters/characters. Also, if it is not too much trouble, would you please give me couple of different solutions. I am using excel 2007. Thanks!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |