Function

[dcb1]

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!

 

[Kevin Smith]

Hello,

if you type into cell C38

=IF(OR(C6=1,C7=1,C8=1),"Yes","No")

you will get your desired affect

 

[JoeU2004]

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]

Thanks for your help--- it worked!

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 -----






- Show quoted text -

 

[dcb1]

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!

 

[Rick Rothstein]

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)
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!

 

[JoeU2004]

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 -----

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!