Standard Excel formula behaves differently depending on which version of Excel I use

user_5464939 0
Viewed 363

I have what I think is a standard formula, which is behaving differently depending on which Excel version I use, Excel 365 or Excel 2019

=IF(F5=$M$1;IFERROR(IF(AND(IFERROR(FIND("CONV";B5;1);"NY");F5=$M$1);"KONV");IF(F5=$M$1;"NY";"ORG"));"ORG")

In the two screenshots below, Excel version 2019 is on top, and Excel version 365 is beneath it. In the first screenshot everything is the same, but in the 2nd picture, "NY" becomes an error in Excel 2019 (which is correct and what I want), but in Excel 365 I get a wrong TRUE.

Matching outputs

Differing outputs

My issue isn't to fix the formula so it works. Rather, I want to know why the difference between Excel 365 and Excel 2019 is there in the first place.

excel-formula excel
1 Answers

Interesting post. With the problem at hand being illogical, the explaination (or rather my hypothesis due to the lack of documentation) is actually the opposite. In short: Dynamic arrays are the culprit to the difference between ms365's and previous versions of handling each condition in the AND() function.

First, even though the ms-documentation tells us that all conditions need to evaluate to TRUE, the reality of it is that there are two other important rules for us to keep in mind:

  • Text values or empty cells supplied as arguments are ignored.
  • The AND function will return #VALUE if no logical values are found or created during evaluation.

Let's assume that with the below examples, the value NY is written in cell A1. In Excel 2019 and earlier versions, you should recieve the following results:

Formula Outcome
=AND(TRUE) TRUE
=AND(TRUE,A1) TRUE
=AND(TRUE,"NY") #VALUE
=AND(TRUE,{"NY"}) TRUE
=AND({"NY"}) #VALUE

Whether it's intentional or not, in versions prior to ms365 any text value will result in an error unless it's written in an array format or pulled in through a cell-reference.

I can only expect the difference with ms365 to be explained that the latter will automatically evaluate the single text-value as an array due to the mechanics of dynamic array functionality.

user_97581940
Related