Hi
I was looking for a little assistance
trying to use the “formula” function that is likely an IF function that looks up a value in one column and then changes the amount on another column to a minus figure
example is below so on the “DCIP” column IF there is either a LC or a SC it would change the amount in the “amount” column to a negative figure. I see functions like the below not sure how it works even after looking at a few articles maybe because my excel skills are not brilliant to start with
if anyone can help would really appreciate it
IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF
thanks
Company | Account Pool Name | DCIP | Currency | Amount | Value | Age | Entry |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LC | CHF | 8,717.83 | 01 Mar 2022 | 118 days | 01 Mar 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LD | CHF | 157,386.00 | 01 Mar 2022 | 118 days | 01 Mar 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SC | CHF | 418,284.85 | 13 Jun 2022 | 14 days | 13 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LD | CHF | 15,459.00 | 21 Jan 2022 | 157 days | 21 Jan 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LC | CHF | 90,271.22 | 20 Jan 2022 | 158 days | 20 Jan 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SD | CHF | 79,058.22 | 20 Jan 2022 | 158 days | 20 Jan 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SC | CHF | 836.20 | 17 Jan 2022 | 161 days | 17 Jan 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LD | CHF | 8,100,964.24 | 07 Jun 2022 | 20 days | 07 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SC | CHF | 450,590.25 | 03 Jun 2022 | 24 days | 03 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LD | CHF | 121,562.00 | 29 Oct 2021 | 241 days | 29 Oct 2021 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LC | CHF | 12,729.75 | 30 Sep 2021 | 270 days | 30 Sep 2021 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LC | CHF | 26,871.85 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LC | CHF | 120,770.00 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LC | CHF | 2,179,390.61 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LD | CHF | 213.68 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LD | CHF | 2,179,390.62 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | LC | CHF | 213.67 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SD | CHF | 7.00 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SD | CHF | 7.00 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SD | CHF | 7.00 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
GLOBAL NOSTROS | UBS02300000070861050000YCHF(BBI) | SD | CHF | 7.00 | 24 Jun 2022 | 3 days | 24 Jun 2022 |
Solved! Go to Solution.
Hi @Ozzym ,
Here's the formula you're searching for :-):
IF [DCIP] = "LC" then -1*[AMOUNT] elseif
[DCIP] = "SC" then -1*[AMOUNT] else [AMOUNT] endif
Greetings,
Seb
Hey @Ozzym,
Your looking for a formula like this:
IF [DCIP] = "LC" or [DCIP] = "SC" THEN -[Amount] ELSE [Amount] ENDIF
If the DCIP column equals the text LC or SC then return the ammount as negative else just return the normal ammount. Note I am updating the ammount column in the formula tool highlighted below:
Other things to note is I had to change your ammount colum to a numeric data type with the select tool and remove the ','s as that makes it text not numeric data.
The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...
Any questions or issues please ask
Ira Watt
Technical Consultant
Watt@Bulien.com
Thank you guys
@Ozzym Please accept Ira Watt's solution if that helped you.