If contains formula with subtraction

[geeklarokcmie]

Column A	Atype	Amnt
123	GPP	40.56
456	CBB	40.51
789	GPP	30.0
101	CBB	80.00
102	AAP	9.00
103	AKS	7.00

What can be the forumla for the below requirement:

The net ‘Amnt’  where the net equals any Atype containing GPP and CBB minus all others

• I tried the below but I was told that it's backwards and adding +1

If Contains([Atype], "GPP","CBB") THEN  -1*[Amnt] +1
ELSE [Amnt] ENDIF

[Felipe_Ribeir0 · Accepted solution]

Hi @geeklarokcmie ,

If i undertood well, you need to use this formula:

IF [Atype] in ('GPP','CBB') then [Amnt]
ELSE -1*[Amnt] ENDIF

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[geeklarokcmie · Accepted solution]

@Felipe_Ribeir0  Appreciate the response. Also, what would be forumla based on the below requirement:

The net ‘Amnt’  where all Atypes with exception of ‘CBB’ are additions. ((If CBB is negative number then everything should be positive number)

[Felipe_Ribeir0 · Accepted solution]

Hi @geeklarokcmie 

Adapting the first formula, it would be:

IF [Atype] in ('CBB') then -1*[Amnt]
ELSE [Amnt] ENDIF

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[geeklarokcmie · Accepted solution]

@Felipe_Ribeir0 When I used the forumla it is only providing with negative values - I need both positive and negative values to be the result