How To Adjust Nested IFS Formula To Include An Additional Column's Condition?

[Dhira]

Hi,
I have a nested IFS formula checking a column "B" for a condition against values in Column A.
Using IFS (or nested IF) statement, I want to override the "IFS" condition if there is any amount in the D column (sometime it'll be black, sometimes, not).

Conditions.xlsx
	A	B	C	D	E
1	%	ROI	Action	Buy Back Amount
2	33.30%	31%	Keep Grinding!
3	67.67%	37%	Take Profit!!!	$ 250.00
4	-16.70%	4%	Keep Grinding!
5		-10%	Watch 👁️
6		12%	Keep Grinding!
7		-18%	SELL/DCA NOW!	$ 130.00
8		32%	Keep Grinding!
9		35%	Take Profit!!!
10		-4%	Watch 👁️	$ 75.00
11
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=IFS(B2=" ","",B2>$A$2,"Take Profit!!!",B2>0,"Keep Grinding!",B2>$A$4,"Watch 👁️",B2<$A$4,"SELL/DCA NOW!")

I've tried adding "=IF(NOT" and "=OR(TRUE" conditions to the formula, but I'm doing them wrong.
Help appreciated.

 

[Eric W]

Try:

=IFS(D2>0,"Buy Back",B2=" ","",B2>$A$2,"Take Profit!!!",B2>0,"Keep Grinding!",B2>$A$4,"Watch 👁️",B2<$A$4,"SELL/DCA NOW!")

 

[Dhira]

Try:

=IFS(D2>0,"Buy Back",B2=" ","",B2>$A$2,"Take Profit!!!",B2>0,"Keep Grinding!",B2>$A$4,"Watch 👁️",B2<$A$4,"SELL/DCA NOW!")

Appreciate the answer.
It worked for the example I gave, but...... not for the "real" spreadsheet I'm using it for.

This is a more complete spreadsheet.
It is Column I that the formula is in. The problem is that it is checking Column K to see if there's any value, BUT it is acting as if there is, even though Column K content is blank.

YIELD.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			33.3%
3			67.67%
4			-16.7%			$ Net Gain	ROI	LP #		Recommended:		Actual $	LP #
5			22.6%		LP #	Or Loss	💵Δ %		Action	Profit To Take	Buy Back Amount
6					6,123,362.76698	$ -	100.0%	6,123,362.767	Buy Back	$ -
7					6,123,362.767	$ 915.91	64.4%	6,123,362.767	Buy Back	$ 619.77
8					6,123,362.767	$ 1,031.31	72.5%	6,123,362.767	Buy Back	$ 697.85		$381.74
9					6,519,021.148	$ 890.78	57.4%	6,519,021.148	Buy Back	$ 602.76
10					6,519,021.148	$ 382.79	24.7%	6,519,021.148	Buy Back			$276.00	1,353,888.29
11					5,165,132.861	$ (108.18)	-7.0%	5,165,132.861	Buy Back
12					5,165,132.861	$ 83.06	5.4%	5,165,132.861	Buy Back			$ 6.32
13					5,190,824.029	$ (32.55)	-2.1%	5,190,824.029	Buy Back			$ 5.92
14					5,220,874.068	$ (206.04)	-13.2%	5,220,873.929	Buy Back		$ 186.76	$ 3.54
15					6,022,108.023	$ (351.00)	-21.3%	6,022,106.929	Buy Back		$ 186.76	$ 1.24
16					6,033,868.231	$ (345.43)	-20.9%	6,033,867.029	SELL/DCA NOW!		$ 186.76	$ 0.32
17					6,037,998.008	$ (317.36)	-19.2%	6,037,996.799	SELL/DCA NOW!		$ 186.76	$ 0.34
18					6,044,331.030	$ (337.67)	-20.4%	6,044,329.819	SELL/DCA NOW!		$ 186.76	$ 0.30
19					6,050,472.55639	$ (255.48)	-15.5%	6,050,471.349	Watch 👁️		$ 186.76	$ 0.78
20					6,060,911.36739	$ (212.59)	-12.9%	6,060,910.149	Watch 👁️		$ 186.76	$ 1.25
21					6,070,832.42291	$ (140.80)	-8.5%	6,070,831.509	Watch 👁️			$ 1.01
22					6,078,222.86934	$ (176.45)	-10.7%	6,078,221.959	Watch 👁️		$ 186.76	$ 1.00
23					6,085,723.66139	$ (237.51)	-14.3%	6,085,722.839	Watch 👁️		$ 186.76	$ 1.25
24					6,097,583.77243	$ (269.26)	-16.2%	6,097,582.939	Watch 👁️		$ 186.76	$ 0.81
25					13,093,012.98552	$ (340.14)	-13.8%	13,093,012.152	Watch 👁️		$ 186.76	$ 1.41
26					13,105,965.05869	$ (364.63)	-14.8%	13,105,964.226	Watch 👁️		$ 186.76	$ 0.71
27					13,126,728.54667	$ (274.23)	-11.1%	13,126,727.926	Watch 👁️		$ 186.76	$ 0.74
28					13,141,930.77092	$ (295.51)	-11.9%	13,141,930.126	Watch 👁️		$ 186.76	$ 0.72
29					13,153,891.64735	$ (297.67)	-12.0%	13,153,890.926	Watch 👁️		$ 186.76	$ 0.72
30					13,168,591.20702	$ (252.36)	-10.2%	13,168,590.426	Watch 👁️		$ 186.76	$ 0.71
31					13,182,967.86791	$ (279.97)	-11.3%	13,182,967.026	Watch 👁️		$ 186.76	$ 0.72
32					13,197,027.90439	$ (272.81)	-11.0%	13,197,027.026	Watch 👁️		$ 186.76	$ 0.63
33					13,210,820.51377	$ (917.00)	-36.9%	13,210,819.626	SELL/DCA NOW!			$ 0.39	35,593,259.08
34					13,228,649.94819	$ (1,127.87)	-45.4%	13,228,649.026	SELL/DCA NOW!			$ 0.34
YIELDFarms

Cell Formulas
Range	Formula
C4	C4	=-C2/2
C5	C5	=C2*C3
I6:I15	I6	=IFS(K6>0,"Buy Back",G6=" ","",G6>$C$2,"Take Profit!!!",G6>0,"Keep Grinding!",G6>$C$4,"Watch 👁️",G6<$C$4,"SELL/DCA NOW!")
J6:J34	J6	=IF(G6<$C$2,"",IF(G6>$C$2,$C$3*F6,IF(G6=" "," ")))
K6:K34	K6	=LET(ROI_diff,LOOKUP(2,1/(M$2:M6<>""),G$2:G6)-G6,IFERROR(IF(ROI_diff>$C$2,LOOKUP(2,1/(M$2:M6<>""),L$2:L6)*($C$3),""),""))
I16:I34	I16	=IFS(G16=" ","",G16>$C$2,"Take Profit!!!",G16>0,"Keep Grinding!",G16>$C$4,"Watch 👁️",G16<$C$4,"SELL/DCA NOW!")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L18:N19,E6:K34	Cell Value	<0	text	NO
I6:I34	Cell Value	contains "SELL/DCA NOW!"	text	NO
J6:K34	Cell Value	>0	text	NO

Note: I used your formula for the first 15 rows, and the rows after (16-34) have the original formula.
I just need Column I to show "Buy Back" if there is a value in Column K, and the other messages depending on the conditions in Column C.
Appreciate the help (please ignore the conditional formatting colors).

 

[Fluff]

How about

=IFS(K6<>"","Buy Back",G6="","",G6>$C$2,"Take Profit!!!",G6>0,"Keep Grinding!",G6>$C$4,"Watch 👁️",G6<$C$4,"SELL/DCA NOW!")

 

[Dhira]

How about

=IFS(K6<>"","Buy Back",G6="","",G6>$C$2,"Take Profit!!!",G6>0,"Keep Grinding!",G6>$C$4,"Watch 👁️",G6<$C$4,"SELL/DCA NOW!")

Perfect, thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.