categories+nested if formulas

hi Guys,

I need a little help with one nested if formula in one of my workflow.

What I am trying to achive:

Categorizing based on service numbers, cat 1 and 2. this works fine.

Based on the categories I need the following:

Scenerio 1 : if category 1 > 2 then retrun 97

Scenario 2: if categroy 2 > 1 , then 3 other scenarios: if [Ctry VAT No.] = [Plant] THEN "2F" and [Ctry VAT No.] IN (B,C,D) THEN "4F"
and [Ctry VAT No.] IN (E,F,G) THEN "F9" . Also if Ctry VAT is null then i just want it to return value "blank"

Attached the simple file for this exercise.

Thanks very much!

test2.xlsx

Developer

Reporting

Accepted answers

JoeS

Hi @EtamSoko

You're welcome! Thanks for uploading the examples.

Looking at the data I could see that there is a data quality issue with the Ctry VAT No. column, it contains trailing spaces:

Which means that Plant and Ctry VAT No. are not the same as one has a space the other doesn't.

I added a trim function to the formula before you used it:

Then I noticed it was still missing some conditions, and you appear to have missed of the first part of mine, so I added that back in:

That should do the trick, let me know if there is anything else it's missing.

test wf1.yxmd

All comments

JoeS

Hi @EtamSoko

I have done it using the formula:

IF [category 1] > [category 2] THEN "97"
ELSEIF IsEmpty([Ctry VAT No.]) THEN "blank"
ELSEIF [Ctry VAT No.] = [Plant] THEN "2F" 
ELSEIF [Ctry VAT No.] IN ("B","C","D") THEN "4F"
ELSEIF [Ctry VAT No.] IN ("E","F","G") THEN "F9"
ELSE "MissedCondition"
ENDIF

I think the main differences was that you had an "and" when you needed an "ELSEIF" and second you need to put quotes around text values in the IN statement

categories+nested if formulas.yxzp

danilang

Hi @EtamSoko

Your input file didn't have any category information, but here's the nested IF

if [Ctry VAT No.] = [Plant] THEN 
	"2F" 
elseif [Ctry VAT No.] IN ("B","C","D") THEN 
	"4F"
elseif [Ctry VAT No.] IN ("E","F","G") THEN 
	"F9"
else 
	"blank"
endif

Dan

Categories+NestedIF.yxzp

EtamSoko

hi @JoeS ,

Thanks for your help. Sorry for the late response - festive season passed.

The solution you proposed doesn't work because now, each time category 2 is bigger than 1, it gives me "MissedCondition" value instead of doing the logical checks.

Apologies if my explanation was not clear, let me try to describe it once more.

if Category 1 is >= 2 then "97" - any other case means then if 2 > then 1 there should be 3 outcomes:

[Ctry VAT No.] = [Plant] THEN "2F"

[Ctry VAT No.] IN (B,C,D) THEN "4F"
[Ctry VAT No.] IN (E,F,G) THEN "F9"

Regardless of the result what is bigger cat 1 or 2, if Ctry VAT No. is empty, then return "blank"

Can you please help me to get this result?

Thanks a lot!

Quick Links

Unanswered
Community Events
Groups

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280