Array If formula
 

I can't get this to work:

{=IF(($AC$136:$AC$146="Yes"),"Yes", "No")}

I want to return the header of data to return Yes once all the details
have been flipped to yes.

Thoughts.

Array If formula
 

Assuming you mean that *every* cell in the range must contain "Yes".

Array entered** :

=IF(AND($AC$136:$AC$146="Yes"),"Yes", "No")

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


" wrote in message
...
I can't get this to work:

{=IF(($AC$136:$AC$146="Yes"),"Yes", "No")}

I want to return the header of data to return Yes once all the details
have been flipped to yes.

Thoughts.

Array If formula
 

Interesting. You could do it the brute-force way
(=IF(AND($AC$136="Yes",$AC$137="Yes"...),"Yes","No "), but that would be
ridiculous if there were more than a handful of them. Let's see...

Oh, of course. Try COUNTIF, to count how many "Yes" values there are in
$AC$136:$AC$146, and if it's 11 say "Yes", otherwise "No". Like this:

=IF(COUNTIF($AC$136:$AC$146,"Yes")<11,"No","Yes")

If the size of the range might vary, get the formula to use the ROWS
function to count how many rows there should be:

=IF(COUNTIF($AC$136:$AC$146,"Yes")<ROWS($AC$136:$A C$146),"No","Yes")

Or name the range and then use the name:

=IF(COUNTIF(Flags,"Yes")<ROWS(Flags),"No","Yes")

--- " wrote:
I can't get this to work:

{=IF(($AC$136:$AC$146="Yes"),"Yes", "No")}

I want to return the header of data to return Yes once all the details
have been flipped to yes.

Thoughts.

Array If formula
 

Hello,

Or
=IF(SUMPRODUCT(--(AC136:AC146<"Yes")),"No","Yes")
entered normally.

Regards,
Bernd

Array If formula
 

Hi,

Let's just take your idea and modify it a tad:

=IF(AND(AC136:AC146="Yes"),"Yes","No")

=IF(OR(AC136:AC146="No"),"No","Yes")
(if by flip you mean the cells contain either Yes or No.)

=IF(OR(AC136:AC146<"Yes"),"No","Yes")
(This works like the first one regardless)

all are array entered.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


" wrote:

I can't get this to work:

{=IF(($AC$136:$AC$146="Yes"),"Yes", "No")}

I want to return the header of data to return Yes once all the details
have been flipped to yes.

Thoughts.

Array If formula
 

Hi,

And one other one

=IF(COUNTIF(AC136:AC146,"<yes"),"No","Yes")

and if the cell are either empty or Yes then
=IF(COUNTIF(AC136:AC146,""),"No","Yes")

both are not array entered.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


" wrote:

I can't get this to work:

{=IF(($AC$136:$AC$146="Yes"),"Yes", "No")}

I want to return the header of data to return Yes once all the details
have been flipped to yes.

Thoughts.