Simplifying an If(OR(…)) statement

  • Thread starter Thread starter xl@lf
  • Start date Start date
X

xl@lf

Is there a way to do the following without using if(OR(…, …, …, ….,
…)) for each of 100 cells? I have a column of 100 cells each
containing a number from 1 through 10. If the number 7 is greater
than any of the 100 cell’s I want it to say “yes”, otherwise “no”..

Example: if(or(7>A1, 7>A2, 7>A3, …, 7>A100), “yes”, “no”)


Thanks
 
Try

=IF(COUNTIF(A1:A100,"<7"),"yes","no")

--

HTH

Bob

Is there a way to do the following without using if(OR(…, …, …, ….,
…)) for each of 100 cells? I have a column of 100 cells each
containing a number from 1 through 10. If the number 7 is greater
than any of the 100 cell’s I want it to say “yes”, otherwise “no”.

Example: if(or(7>A1, 7>A2, 7>A3, …, 7>A100), “yes”, “no”)


Thanks
 
Thanks Bob, that’s thinking outside the box. Now I’d like to swap out
the 7 with a cell reference. Such that
=IF(COUNTIF(A1:A100,"<E5"),"yes","no") but that's not working.

Example: if(or(E5>A1, E5>A2, E5>A3, …, E5>A100), “yes”, “no”)
 
Just what I was looking for, another outside the box thinker. Swap
"7" with "E5" and it works great, Thanks!!!
 
Try

=IF(COUNTIF(A1:A100,"<"&E5),"yes","no")

--

HTH

Bob

Thanks Bob, that’s thinking outside the box. Now I’d like to swap out
the 7 with a cell reference. Such that
=IF(COUNTIF(A1:A100,"<E5"),"yes","no") but that's not working.

Example: if(or(E5>A1, E5>A2, E5>A3, …, E5>A100), “yes”, “no”)
 
Back
Top