cancel
Showing results for
Show  only  | Search instead for
Did you mean:
Choose Language Hide Translation Bar
Level III

## Formula Editor

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!

6 REPLIES 6
Super User

## Re: Formula Editor

``Sum( :a>0 & :b>0, :c>0 & :d>0, :e>0 & :e>0);``
Jim
Level III

## Re: Formula Editor

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.

Level III

## Re: Formula Editor

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

If(

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

1,

0

)

Level VII

## Re: Formula Editor

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.

Georg
Super User

## Re: Formula Editor

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.

Jim
Staff

## Re: Formula Editor

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)``