Excel formula is annoying and appears too complex for Alteryx...

[Paul_s_Moody]

Hi,

I'm replacing a "horrible" Excel spreadsheet with a workflow, and although the formula is easy to understand in Excel I've not found a way to create it in Alteryx Designer...

AD3=IFERROR(IF(AB3="Limited","Limited","Fixed"),IFERROR(IF(AA3="Limited","Limited","Fixed"),AC3))

The IFERROR(...) formula have been added because column AB can contain #N/A "error values" as it was generated by an non-trapped error lookup

Column AA may also contain percentage numbers as well as N/A string values (and the #N/A error values).

Column AC, used as the default only contains valid strings or (blanks)

Both columns also contain the target "Limited" and "Fixed" strings too!

I'm sure there must be a way, but I can't see anything obvious...

Columns AA, AB and AC exist at the point in the workflow where I'm (hoping) to use a formula tool to add a new column AD.

Thanks in advance,

Paul

[BS_THE_ANALYST]

@Paul_s_Moody any chance you can put a snippet of the column(s) (or sample data) and the expected output? I'm sure we can build the logic!  

[Paul_s_Moody]

Hi, I've added a sample of the column file.

Keeping it consistent to my original example, the data starts in column AA with the new column added in AD

Regards,

Paul

[BS_THE_ANALYST · Accepted solution]

@Paul_s_Moody Just got round to this. Two screenshots, one with your expected output, one with the output after using Alteryx formula:
1. Formula in Alteryx:

2. Your expected output

3. Side by side view

4. Formula to use for your column:
IF [GMP Revaluation Type] = "Limited" THEN "Limited"
ELSEIF [GMP Revaluation Type] = "#N/A" THEN
(
IF [GMPRevalRate] = "Limited" THEN "Limited"
ELSEIF [GMPRevalRate] = "#N/A" THEN [GMP Reval Rate]
ELSE "Fixed"
ENDIF
)
ELSE "Fixed"
ENDIF

I'll attach the workflow aswell. If it doesn't work, just @ me and we'll get it sorted.

[Paul_s_Moody]

Thank you!

It was the ability to embed an additional IF clause in (...) within the main clause that I was missing!

I'd not seen any examples of this so many thanks.

Knowing this will make other formula easier to deal with too!

Regards,

Paul