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.

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

Relevance
  • Anonymous
    1 decade ago
    Favorite Answer

    You need to get into visual basic programming for this.

  • 4 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

Still have questions? Get your answers by asking now.