Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
how to use excel's AND function in array formula?
I have this formula which goes through a lengthy list (F$13:F$5659) and gives me the largest number of concurrent cells that are greater than some number(>=$A$2). That seems to work fine for me however, I need to take this formula and add a second condition i.e. the cell must be a number. Notice that it is an array formula.
Works: {=LARGE(FREQUENCY(IF(F$13:F$5659>=$A$2,ROW(F$13:F$5659)),IF(F$13:F$5659<$A$2,ROW(F$13:F$5659))),$C2)}
Doesn’t Work: {=LARGE(FREQUENCY(IF(AND(F$13:F$5659>=$A$2,ISNUMBER(F$13:F$5659)),ROW(F$13:F$5659)),IF(F$13:F$5659<$A$2,ROW(F$13:F$5659))),$C2)}
Also Doesn’t Work: {=LARGE(FREQUENCY(IF(ISNUMBER(F$13:F$5659),IF(F$13:F$5659>$A$2,ROW(F$13:F$5659))),IF(F$13:F$5659<$A$2,ROW(F$13:F$5659))),$C2)}
I think it is because the output of AND is true or false where I need it to be an array. I can seem to get around this data type mismatch. Do I need to be changing the bins on my frequency formula? I think I’ve reached the limitations of my ability in excel and have just lost my patience for the day, please help.
Similar issue with this formula, I can’t get the isnumber to behave like I want it to.
Works: {=COUNTIF(F$13:F$5659,">=1500")}
Fails: {=COUNTIF(F$13:F$5659,AND(IF(">2100",TRUE,FALSE),ISNUMBER(F$13:F$5659)))}
Fails: {=IF(ISNUMBER(F$13:F$5659),COUNTIF(F$13:F$5659,">2100"))}
2 Answers
- Anonymous1 decade agoFavorite Answer
You need to get into visual basic programming for this.
- rudicilLv 44 years ago
specific, specific Use this function... =SUM( IF(( Criteria1 = A11) * ( Criteria2 = $W$2), oblique( $S$2))) the place S2 has your fee Aug, in case you have a named selection with "aug" then oblique will convert this text into ref and use it oblique is an invaluable function to transform text cloth that characterize ref into ref Mail me for greater records study my profile, i'm the XLMan