IF statement issue

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!

Workflow

Accepted answers

BenMoss

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.

All comments

BenMoss

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.

yzhang316

I edited the code based on your suggestion. Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703