Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
When I says merge I mean with the help of nested if
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
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.
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