Problem with the If Condition

[Anonymous]

Hi Geniuses,

I have two big if conditions below, I want to merge those two conditions into 1 if condition so as to put it into the expression

=if(([Actual]>[Budget]) and (ACCOUNT='PL924100T' OR ACCOUNT='PL924000T' OR ACCOUNT='PL923100T' OR ACCOUNT='PL411810'  OR ACCOUNT='PL411350T' OR ACCOUNT='PL411150T' OR ACCOUNT='PL518000T' OR ACCOUNT='PL923200T'

OR ACCOUNT='PL923000T' OR ACCOUNT='PL411200T' OR ACCOUNT='PL922100T' OR ACCOUNT='PL922000T' OR ACCOUNT='PL411900T' OR ACCOUNT='PL430000T' OR ACCOUNT='PL411990'  OR ACCOUNT='PL921000T' OR ACCOUNT='PL920000T'

OR ACCOUNT='PL900000T' OR ACCOUNT='PL990000T'),

if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))

,

if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))

)

if(([Actual]>[Budget]) and (ACCOUNT='PL511000T' OR ACCOUNT='PL519000T' OR ACCOUNT='PL515000T' OR ACCOUNT='PL923300T' OR ACCOUNT='PL512000T' OR ACCOUNT='PL922200T' OR ACCOUNT='PL922300T' OR ACCOUNT='PL922400T'

OR ACCOUNT='PL516000T' OR ACCOUNT='PL517000T'

),

if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))

,

if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))

)

Thanks in advance

S

[Anonymous]

When I says merge I mean with the help of nested if

[zhadrakas]

i would recommend you to create a variable in script:

LET vAccountsArray = 'PL511000T', 'PL922400T'; .... (all your accounts)

Then build your IF Statement like this:

if(([Actual]>[Budget]) and match(ACCOUNT, '$(vAccountsArray)',

       if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100))),

             if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100)))

Regards

tim

[Anonymous]

creating the variable will only reduce the size of the if but still there are lot of conditions which I need to accomodate in nested if

this is where I got stuck bro.

[effinty2112]

Hi Shashank,

This expression combines the two and will be easier to maintain

if([Actual]>[Budget]) and

Match(ACCOUNT,

'PL924100T','PL924000T','PL923100T','PL411810','PL411350T','PL411150T','PL518000T','PL923200T',

'PL923000T','PL411200T','PL922100T','PL922000T','PL411900T','PL430000T','PL411990','PL921000T',

'PL920000T','PL900000T','PL990000T','PL511000T','PL519000T','PL515000T','PL923300T','PL512000T',

'PL922200T','PL922300T','PL922400T','PL516000T','PL517000T'),

if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))

,

if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))

)

I'm curious about the last part of your expression.

if(-(fabs(([Var]/[Budget]) * 100))<-100, ...

is the same as

if((fabs(([Var]/[Budget]) * 100))>100, ... which appears earlier in your expression

(multiplying an inequality by a negative number reverses the sign, '<' becomes '>')

We can simplify further to

if(fabs(([Var]/[Budget]))>1, ...

If Budget is >0 then

if(fabs(Var)>Budget, ...

Hope this helps.

Andrew