Formula Editor

[#1]

Hi,

I'm trying to write a formula that will check if the values in Column A & Column B are both over zero OR if the values in Column C & Column D are both over zero (and so on with many column pairs).  If at least one pair has both values over zero, I would like the formula to return 1.  If not, return 0.  However, the formula I wrote is returning all 1's... even in rows that have all negative numbers.  Can someone please tell me what I'm doing wrong?  Thank you!

[#2]

Sum( :a>0 & :b>0, :c>0 & :d>0, :e>0 & :e>0);

[#3]

Hi Jim,

Thank you for the suggestion, but this seems to be summing the numbers.  I want to end up with an indicator column (zeros and ones).  If "1", that would mean at least one column pair had both numbers over zero.

[#4]

This is the formula that I have that returns all 1's:

If(

(:A & :B) >= 0 | (:C & :D) >= 0 | (:E & :F) >= 0,

1,

0

)

[#5]

Dear @EH1 

please recognize that ":A & :B" will always yield 1, if not at least one value of both is exactly zero. And comparison with ">= 0" consequently also yields 1.

So you would have to construct your formula another way: see proposal of Jim ":a>0 & :b>0" will work correctly, and sum is working like or, except that you might put an "if > 0" statement around to get only 0 or 1. 

In formula editor below you can see the result of selected subexpression as well to better understand.

[#6]

The interpretation of the formula I specified is the following.

:A>0 & :B>0 will return a 1 if true and a 0 if not.  Therefore :A=1 & :B=0 returns a 0 while :A=5 & :B=2 returns a 1.  Ahd since each of these comparison clauses are in a Sum() function, it will add up all of the zeros and ones.

[#7]

This expression returns the Boolean value that you want in the column. You do not need the If() function unless need conditional action in the same formula. It will return 0 (false) or 1 (true) as desired.

(:a>0 & :b>0) | (:c>0 & :d>0) | (:e>0 & :e>0)