Issues creating multiply IF statement from excel into Alteryx

[sureslala]

Hello

I'm having trouble reproducing a multiple excel IF statement into Alteryx. I've broken down the excel 'IF Statement' into 2 formula's in Alteryx (workflow attached) which seem to work for all the scenarios except for where all the conditions are true(first formula) which is bafflingly me.

The attached excel sheet is the raw data with column G (highlighted) the expected output. My formula's work for all the rows except the last 2, rows 9 and 10, where the output should be row 9 = 14,240,000 and row 10 = 5,760,000

My workflow produces (16,287,156) and (6,588,063) for rows 8 and 9 instead of 14,240,000 and 5,760,000. My Alteryx calculation is multiplying [ -(TP_Nominal) * FX ] instead of just displaying 'TP_Nominal'. When I look at my formula, I can't work out why this is occurring as all the criteria are true? 

Any assistance will be much appreciated and will help me get a good nights sleep! as I've been mulling over this IF statement issue for a few nights now :(

Thanks!

Sures

[AngelosPachis]

@sureslala the formula displayed in your screenshot works fine and it's not what's returning the negative values in the highlighted cells. It's your second calculation that does this.

So since those records don't contain TRS in the field [TP_STRTGY] and [TP_BUY] is B, they go to the ELSE clause of the IF statement

[sureslala]

Oh Thank you Angelos!

Could you assist me in combining these 2 formula's into one that produces the same results as the excel formula pls?

=IF(LEFT(FZ1234,3)="TRS",IF(RIGHT(O1234,3)="MP7",IF(F1234="B",IF(R1234="LN_BR",N1234,-N1234*GB1234),N1234*GB1234),-N1234*GB1234),IF(F1234="S",N1234*GB1234,-N1234*GB1234))

Thanks

Sures

[binuacs · Accepted solution]

@sureslala can you replace the excel field names with alteryx field names?

IIF(LEFT(FZ1234,3)="TRS",IIF(RIGHT(O1234,3)="MP7",IIF(F1234="B",IIF(R1234="LN_BR",N1234,-N1234*GB1234),N1234*GB1234),-N1234*GB1234),IIF(F1234="S",N1234*GB1234,-N1234*GB1234))

[sureslala]

Hi binuacs

I've tried but formula's not working, probably missing parentheses, will keep trying. The field names are

FZ      TP_STRTGY

O        TP_PFOLIO

F        TP_BUY

R       TRN_GRP

N       TP_NOMINAL

GB     FX

Thanks!

[sureslala]

just can't get 'ENDIF' to work?

[Luke_C · Accepted solution]

Hi @sureslala 

You don't need ENDIF if you're using IIF statements. Just for normal IFs.