topic Formula Editor in Discussions

Formula Editor

EH1 — Fri, 09 Jun 2023 15:58:33 GMT

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!

Re: Formula Editor

txnelson — Wed, 14 Sep 2022 19:53:28 GMT

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

Re: Formula Editor

EH1 — Wed, 14 Sep 2022 20:38:25 GMT

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.

Re: Formula Editor

EH1 — Wed, 14 Sep 2022 20:43:38 GMT

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

If(

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

)

Re: Formula Editor

Georg — Wed, 14 Sep 2022 21:06:15 GMT

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.

Re: Formula Editor

txnelson — Thu, 15 Sep 2022 03:26:19 GMT

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.

Re: Formula Editor

Mark_Bailey — Fri, 16 Sep 2022 13:35:14 GMT

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)