If Function

  • Thread starter Thread starter Christa
  • Start date Start date
C

Christa

Hi, I'm struggling with a formula. I want excel to test
cell A1 if it contains a 1 and the range C1:G1 contains
any of the following values 3,4,5,6,7 the result should
be "Yes", if not "No". I think I need to combine if(and
with an if(or but I dont know how to.

=if(a1=1 and the range C1:G1 contains 3-7, "true", "False"

Is there a boffin out there who can assist me please!
 
Boffin? Hmmmm...that's new to me.

Try:

=IF((A1=1)*(SUM(N(C1:G1={3;4;5;6;7}))),"Yes","No")

Array-entered, meaning after copying the formula into a
cell, hold down <Ctrl> and <Shift> and press <Enter>.

HTH
Jason
Atlanta, GA
 
Jason Morin said:
Boffin? Hmmmm...that's new to me.
Click to expand...

I believe it's a Briticism.
Try:

=IF((A1=1)*(SUM(N(C1:G1={3;4;5;6;7}))),"Yes","No")

Array-entered, meaning after copying the formula into a
cell, hold down <Ctrl> and <Shift> and press <Enter>.
Click to expand...

The following non-array formula seems to work also:

=IF(AND(A1=1,(COUNTIF(C1:G1,"<=7")-COUNTIF(C1:G1,"<3")>0)),"Yes", "No")

Allan Rogg
 
Back
Top