OR statement with range of cells

[ArtySin]

Hi, I have a formula which work well for 4 cells with the same condition to
give the result in another cell:
=IF(OR(J245="Fail",J246="Fail",J247="Fail",J248="Fail"),"F","P")
I now however, need to do the same thing for a range of cells that is more
than 2
0 in total. I've tried naming a range but that comes back with #VALUE! as
the result. Anone any idea how I can do this at all?
Many thanks

 

[Pete_UK]

You could try something like this:

=IF(COUNTIF(J245:J265,"Fail")>0,"F","P")

If any of the cells in the range J245:J265 have Fail, the formula will
return F.

Hope this helps.

Pete

 

[T. Valko]

As long as the range of cells in question is a contiguous block I would use
Pete's suggestion.

You could still use OR but it would have to be array entered** :

=IF(OR(J245:J265="Fail"),"F","P")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[ArtySin]

As long as the range of cells in question is a contiguous block I would use
Pete's suggestion.

You could still use OR but it would have to be array entered** :

=IF(OR(J245:J265="Fail"),"F","P")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP






- Show quoted text -

Thanks V much guys now works perfectly

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

As long as the range of cells in question is a contiguous block I would
use
Pete's suggestion.

You could still use OR but it would have to be array entered** :

=IF(OR(J245:J265="Fail"),"F","P")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP






- Show quoted text -

Thanks V much guys now works perfectly

 

[Bernd P]

Hi, I have a formula which work well for 4 cells with the same condition to
give the result in another cell:
=IF(OR(J245="Fail",J246="Fail",J247="Fail",J248="Fail"),"F","P")
I now however, need to do the same thing for a range of cells that is more
than 2
0 in total. I've tried naming a range but that comes back with #VALUE! as
the result. Anone any idea how I can do this at all?
Many thanks

Another one, just for fun:
=IFERROR(REPT("F",SIGN(MATCH("Fail",J245:J299,0))),"P")

Regards,
Bernd