Have Geek Brain

Prevent Excel from evaluating unnecessary expressions in OR ()

IF(OR(ISNA(MATCH(8,B10:B17,0)),MATCH(8,B10:B17,0)>8),"",...BLAH...)

I understand how to fix this problem by changing the formula. I have a way to show this. You can see that the OR () check checks if the first MATCH () returns NA. When this happens, OR () should automatically return TRUE and not evaluate the second MATCH (), because the conditions were met for OR () to return true no matter what other arguments exist. You will notice that the first and second MATCH () functions do the same thing. What happens is the whole function returning NA, because the second MATCH () is executed, although this is not required, OR () is satisfied with one TRUE, so the function must return "".

Is this a bug or is this intentional?

+2


a source to share


2 answers


Excel OR formulas do not evaluate short circuits (there is also no AND), but IF does. I guess this is intentional, as it has always been the case.



+4


a source


To expand on what Charles said, OR and AND cannot be ingrained because of what they are. The formula evaluates all inputs to get a result. That being said, you can probably write a UDF that will do what you ask.



0


a source






More articles:


All Articles