Between a range of Positive and Negative

[#1]

Hi,

I want to compare if a value is falling under Positive or Negative

For Example:

Target_Table:

Country    Target     Month

ABC           125          Jan

ABC           136          Feb

Compare_Table:

Country        Value    Month

ABC              100       jan

DEF               150      Feb

GHI                200      March

So if values is in the range of positive/Negative for a particular month/Year.

E.g- If the value of ABC in between +100 to -100 of Target, its true else False.. In the above table For country ABC, Target is 125 for Jan which is between +100 to -100. So it's True.

Thanks in advance.

[#2]

Tried with IF(num(sum(Target),'##,##0;(#,##0)')<=Value or num(sum(Target),'##,##0;(#,##0)')>= -Value, True,False)

Not getting what exactly I'm looking for though.

Any comments?

Target_Table:

Country    Target     Month

ABC           125.12345          Jan

ABC           136.23465          Feb

Compare_Table:

Country        Value    Month

ABC              100       jan

DEF               150      Feb

GHI                200      March

Thanks in advance.

[vishsaggi]

Can you explain a little more what you trying to expect the output. And if you run these two tables synthetic will be formed. However, can i know on what basis that table1 target 125 falls between 100 and -100. You mean 125 >= -100 and 125 <= 100 ? It will be false here? Correct me ?

[#4]

Hi,

Thanks for the reply.

Both the tables I gave above are for reference and are sample ones.

I want to compare Sum(Target) for a month and plant with the Value in Compare_Table.

Let's say for Jan month, ABC plant has Target value as 125.12345. So if this value falls in the range between Negative or positive of 100 I.e from Compare_Table it'll be considered as Pass else Fail.. In the above scenario 125.12345 is not in the range of +100 to -100. So it's Fail.

Thanks in advance.

[boopeshj]

Hi gtripathy

It worked for me.

if( (sum(Target) < sum(Value)and sum(Target) > (-1 * sum(Value))), 'True' , 'False') . Let me know if any concern.

Regards

Boopesh

Please mark correct if you find them

[vishsaggi]

Can you try this ?

[#7]

Sorry for the late reply.

IF(num(sum(Target),'##,##0;(#,##0)')<=num(sum(Value),'##,##0;(#,##0)' )AND num(sum(Target),'##,##0;(#,##0)')> -num(sum(Value),'##,##0;(#,##0)') True,False)

Else Tweaking what Boopesh posted.

Thanks again.

[vishsaggi]

Did you resolve your issue if not can you post the expected output you are looking for ?

[#9]

Yes it was resolved.. Thanks again.