Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field [Statement Total Minus LH Total] that will contain positive(+) and negative(-$) amounts. I need to do a stoplight for any amount that is >='$5,000,000.00' and >='-$5,000,000.00.
I have these 2 expressions, but the Negative does not work - any suggestions? I would like to combine into 1 expressions that returns a 0 for False, 1 for True
Positive
=if([Statement Total Minus LH Total]>='$5,000,000.00',1,0)
Negative
=if([Statement Total Minus LH Total]<='($5,000,000.00)',1,0)
What if you do this?
Should handle both + and - numbers by using fabs
=If(fabs(Num([Statement Total Minus LH Total])) >= 5000000, 1, 0)
Dan Maczko wrote:
I have a field [Statement Total Minus LH Total] that will contain positive(+) and negative(-$) amounts. I need to do a stoplight for any amount that is >='$5,000,000.00' and >='-$5,000,000.00.
The bold part is a type right?
you meant <= '-$5,000,000.00', right?
is your field a number or a text?
I assumed it is number field because the positive side is giving him the desired result.
=if( ([yourfield]>= 5000000) or ([yourfield] <= -5000000),1,0)
Got it to work with this-
=If(Fabs(Num([Statement Total Minus LH Total]))>='$5,000,000.00',1,0
As always, Thanks a million!!
Dan
Great
If you got what you wanted, please mark the correct and helpful answers accordingly to close the thread.
Best,
Sunny