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

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

Datasets

Developer Tools

Input

Output

Join

Data Investigation

Accepted answers

BS_THE_ANALYST

@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.

Excel_nested_if.yxzp

All comments

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

SampleGMP_Data.xlsx

BS_THE_ANALYST

@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

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

Excel_nested_if.yxzp

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703