IF statement issue

[yzhang316]

Hi There,

I am trying to come up with an formula which in the active row is close to 0 when plus or  minus row+1, and row-1.  I have the following formula, but the formula is still picking up sum_value that are not <=0.0001 or >-=0.0001.  Am I missing something..?

IF([Row-1:Sum_Value]+[Sum_Value]<=0.0001 OR [Row-1:Sum_Value]+[Sum_Value]>=-0.0001) THEN 0

ELSEIF ([Row+1:Sum_Value]+[Sum_Value]<=0.0001 OR [Row+1:Sum_Value]+[Sum_Value]>=-0.0001) THEN 0

ELSE [Sum_Value]

ENDIF

Thanks!

[BenMoss · Accepted solution]

Can you post some sample data, though I think I know where the problem lies..

IF([Row-1:Sum_Value]+[Sum_Value]<=0.0001 OR [Row-1:Sum_Value]+[Sum_Value]>=-0.0001) THEN 0

Because you are using an OR statement here everything is going to evaluate to 0.

Lets take a few examples

0.7 IS THIS <=0.0001 FALSE OR IS THIS >-0.0001 TRUE THEREFOR TRUE THEREFOR 0

0.000005 IS THIS <=0.0001 TRUE OR IS THIS >-0.0001 TRUE THEREFOR TRUE THEREFOR 0

Every single number in the world here is going to evaluate to zero...

I think you want AND instead of OR.

[yzhang316]

Thank you for helping and sorry for the late reply...

Original

Invoice #	Amount
101	45
102	-45
103	100
104	-110
105	-15
106	15

After

Invoice #	Amount	Net Zero
101	45	0
102	-45	0
103	100	100
104	-110	-110
105	-15	0
106	15	0

 Each cell in Net Zero will have 2 equations: add [row+1], and [row-1], either equations result in between -0.001>= and <=0.001 will enter as 0.  

[jasperlch]

Hi @yzhang316

As pointed out by @BenMoss, the problem lies in the logic of your formula in your IF condition

x <= 0.0001 OR x >= -0.0001 will always result in TRUE no matter what x is

So, what you probably want is an AND operator instead of OR.

[yzhang316]

I edited the code based on your suggestion.  Thanks!

[yzhang316]

Yes, I fixed it based on the suggestion.  Thanks!