Is this a bug in Excel formula evaluation? If not please explain expression evaluation


Insert values in cells as follows: A1:2, B1:0, C1:1
and in cell D1 the formula: =IF(A1>1*AND(B1>0)*AND(C1=0),0,1) –> 0 (incorrect (?) result)

Excel from Office Professional Plus 2021 Version 2205

I do not claim that this is a good formula; it is from a question asked of me, where the intention was =IF(AND(A1>1,B1>0,C1=0),0,1) or more simply =--(NOT(AND(A1>1,B1>0,C1=0)))

Yet it is valid in terms of Excel formula syntax and gives – in terms of my understanding of the syntax – an incorrect result

Here are some things I’ve tried

  1. =A1>1, =B1>0, =C1=0 –> TRUE, FALSE, FALSE respectively
  2. =A1>1, =AND(B1>0), =AND(C1=0) –> TRUE, FALSE, FALSE
  4. =IF(TRUE*FALSE*FALSE,0,1) –> 1

all as expected

Fiddling with cell values suggests (I can’t claim to have tested comprehensively) that

  • values in B1 and C1 do not matter
  • the result "flips" (from 0 to 1) when setting A1 to any numeric value <= 0
    (even more confusing because the result of evaluating A1>1 should flip at 1)

>Solution :

Its very simple.
You are getting the result for =IF(A1>(1*AND(B1>0)*AND(C1=0)),0,1) but it seems like you want the result for =IF((A1>1)*AND(B1>0)*AND(C1=0),0,1).

Set your brackets accordingly and read up on Calculation operators and precedence in Excel

Leave a Reply Cancel reply