0

Question

I'm using named ranges extensively in my workbook, but now find that they are not a simple substitute for regular ranges in the case of the AND function. In that case the formula is no longer "spilled" over to subsequent rows and only 1 value is calculated. How can I fix that behavior?

Dummy data

This is my dummy data:

colu1 colu2
3 0
0 2
1 9
2 1

Attempts

Successfully using single condition

I want to check if both colu1 and colu2 are not equal to zero. Checking this for a single column works as expected.

Formula's (the formula "spills" over by itself, I don't have to drag it down) on the left and result on the right:

colu1<>0? colu2<>0? colu1<>0? colu2<>0?
=IF(colu1<>0, 1, 0) =IF(colu2<>0, 1, 0) → 1 0
(SPILLS) (SPILLS) → 0 1
(SPILLS) (SPILLS) → 1 1
(SPILLS) (SPILLS) → 1 1

Failing when using multiple conditions

Checking both columns at the same time in an AND formula fails:

Formula's (in this case the formula does not "spill" anymore) on the left, result on the right:

both<>0? both<>0?
=IF(AND(colu1<>0, colu2<>0), 1, 0) → 0
(NO SPILL) → (EMPTY)
(NO SPILL) → (EMPTY)
(NO SPILL) → (EMPTY)
Saaru Lindestøkke
  • 2,067
  • 1
  • 25
  • 51

2 Answers2

3

AND takes array inputs and outputs singular result not an array. Instead use *:

=IF((colu1<>0)*(colu2<>0), 1, 0)

enter image description here

Or if you have Excel 2013 or later you can use BITAND():

=IF(BITAND(colu1<>0,colu2<>0),1,0)

But note that BITAND requires two and only two arguments, and will not work with more. It works in this case as we are basically comparing 1's and 0's. This is not a silver bullet for using AND in Array formulas. And thus the best method remains the multiplication of Booleans.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This is readable, and close to the syntax I'm used to, thanks. Does `BITAND` achieve the same? – Saaru Lindestøkke Apr 19 '21 at 21:30
  • Holy Crap, I did not know about BITAND(). It actually works and returns an array: `=IF(BITAND(colu1<>0,colu2<>0),1,0)` – Scott Craner Apr 19 '21 at 21:32
  • Note that it works in this case and would work if only two Booleans. It cannot be expanded to 3 or more. – Scott Craner Apr 19 '21 at 21:39
  • 1
    @ScottCraner That's a new one for me too. But, for > 2 ranges, `BITAND(BITAND(colu1<>0,colu2<>0),colu3<>0)` – chris neilsen Apr 19 '21 at 22:15
  • @chrisneilsen sure can, but at what point is it quicker to just multiply Booleans? This is one of those diminishing returns. – Scott Craner Apr 19 '21 at 22:31
  • 1
    @ScottCraner sure, in this case "multiplication of Booleans" works well. And since it's _Bitwise_ AND, it's a bit of overkill for Boolean tests anyway. If it's quicker (to enter, or to execute?) to use BITAND or MoB would need to be tested, probably not relavent to _this_ Q. FWIW, having discovered a new function, I like to explore its potential. And "cannot' is a bit of a red rag to me. – chris neilsen Apr 19 '21 at 22:39
1

Or skipping IF: =(--(colu1*colu2<>0))

P.b
  • 8,293
  • 2
  • 10
  • 25