SOLVED

Adding additional condition to Cell Formula

Copper Contributor

I am using the following to convert a value to a negative number from cell (E2) into a new cell (D2) when cell (I2) equals "Discover" and it works fine.  But I want to add an exception to not convert to negative into (D2) if another cell (J2) value has(equals) "Payment".  If it does equal "Payment" just Set (D2) to (E2).

For a cell D2 I am using the following with success:

=IF(I2="Discover",(E2*(0-1)),E2)

Now I want to add another condition(or exception) to the above:  Even if I2 is "Discover" but cell J2 has "Payment" then just set D2 to E2 (even when (I2) is "Discover"

Appreciate any tips, Thank You

 

5 Replies

@Wade_Grimm 

As variant

=IF(J2="Payment", E2, IF( I2="Discover", -E2, E2) )

@Sergei Baklan 

Thank you Sergei, i tried this as a cell logical formula but it errored out saying to many arguments.  I messed around with parentheses a bit trying to make a single statement but no luck. 

@Wade_Grimm 

@Sergei Baklan's formula should work. but here is an alternative:

 

IF((I2="Discover")*(J2<>"Payment"), -E2, E2)
best response confirmed by Wade_Grimm (Copper Contributor)
Solution

@Wade_Grimm 

Please check it in attached file

image.png

Got it working using the following:
=IF(AND(D67="Discover",E67="PAYMENT"),0,G67*(0-1))
And I added another value of zero. Thanks for your help. Set me on the right trail. Bought a book - haha See sample data output below.

 

                                          New
Source(D67)    For(E67)    AmountF(67)  Amount(G67)
Discover       Restaurant   -160.94         160.94
Discover       Payment            0.00     -1,500.00
Discover       Payment            0.00     -1,615.73
Discover      Payment            0.00     -1,500.00
Discover      Payment            0.00     -1,592.07
Discover      Payment            0.00     -1,500.00
Discover     Restaurant      -71.10        - 71.10
Discover     Restaurant    -107.17        107.17
Discover     Payment            0.00       -500.00
Discover    Payment            0.00       -1,000.00
Discover    Payment            0.00       -1,000.00
Discover Restaurant      -37.46            37.46
Discover Restaurant      -15.23            15.23

1 best response

Accepted Solutions
best response confirmed by Wade_Grimm (Copper Contributor)
Solution

@Wade_Grimm 

Please check it in attached file

image.png

View solution in original post