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!
Solved! Go to 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.
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.
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.
I edited the code based on your suggestion. Thanks!
Yes, I fixed it based on the suggestion. Thanks!