Change Sign in a Cell When Another Cell Includes but is not Limited to Certain Text

[jdpro]

Hello

Using the formula: =IF(J43="buy",M43*-1,M43)

I would like to be able to have "buy" and other text in the reference cell and still have it change the result cell to negative when it sees the word "buy". There will be different "buy" items and it would be great to have it in one column rather than having to add another column to show what is being bought. So can a variable be added to the function?

Thanks!

 

[dreid1011]

Can you post a sample of your data and the expected results? Your description is not very clear what you want to achieve.

 

[jdpro]

Thanks for asking, dreid1011.

Date	Action	Qty	Price	Amount	Cost
11/14/23	Sell Gadget 1-240308R	1	17.33	$17.33	$17.33
11/20/23	Buy Widget 46	1	2.02	$2.02	$(2.02)
12/26/23	Buy Thing 7	100	50.23	$5,023.00	$(5,023.00)

In the Action column is the key word "Buy" that I would like to reference, so as to make the value in the Cost column a negative. But I would like to say Buy x and not just "buy." Otherwise I would have to add another Item column. Does this help clarify?

 

[dreid1011]

See if this does what you want:

Book1
	I	J	K	L	M	N
42	Date	Action	Qty	Price	Amount	Cost
43	11/14/2023	Sell Gadget 1-240308R	1	17.33	$17.33	$17.33
44	11/20/2023	Buy Widget 46	1	2.02	$2.02	($2.02)
45	12/26/2023	Buy Thing 7	100	50.23	$5,023.00	($5,023.00)
Sheet1

Cell Formulas
Range	Formula
N43:N45	N43	=IF(ISNUMBER(SEARCH("Buy",J43)),M43*-1,M43)

 

[jdpro]

Thanks for that really fast fix to my issue! I also am wondering about column M and N. Is it possible to consolidate so I don't have to have both?

 

[dreid1011]

Like this?

Book1
	I	J	K	L	M
48	Date	Action	Qty	Price	Amount
49	11/14/2023	Sell Gadget 1-240308R	1	17.33	$17.33
50	11/20/2023	Buy Widget 46	1	2.02	($2.02)
51	12/26/2023	Buy Thing 7	100	50.23	($5,023.00)
Sheet1

Cell Formulas
Range	Formula
M49:M51	M49	=IF(ISNUMBER(SEARCH("Buy",J49)),(K49*L49)*-1,(K49*L49))

 

[jdpro]

Fantastic!

 

[dreid1011]

You're welcome.

 

[Peter_SSs]

If you are interested, here are a couple of shorter options.

24 03 09.xlsm
	I	J	K	L	M	N
42	Date	Action	Qty	Price	Amount	Amount
43	11/14/2023	Sell Gadget 1-240308R	1	17.33	17.33	17.33
44	11/20/2023	Buy Widget 46	1	2.02	-2.02	-2.02
45	12/26/2023	Buy Thing 7	100	50.23	-5023	-5023
Amount

Cell Formulas
Range	Formula
M43:M45	M43	=K43*L43*IF(COUNTIF(J43,"*buy*"),-1,1)
N43:N45	N43	=K43*L43*(1-2*COUNTIF(J43,"*buy*"))

 

[jdpro]

I'll play with it Peter, thanks!