Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array If formula
Hello,
Or =IF(SUMPRODUCT(--(AC136:AC146<"Yes")),"No","Yes") entered normally. Regards, Bernd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |