Years visible only if there's a value in cell

[tigerzen]

After a formula that only displays a yearly value when there's a negative value in column F, in rows 14, 15 etc the years should not be displayed. Columns D to F are a table, I've tried to write a formula using ISBLANK but I keep getting a circular reference.

Book7
	A	B	C	D	E	F
1				Year	Periods	FV
2				1	12	-$229,500.00
3	Assumed growth	5.00%		2	24	-$209,000.00
4	Rate per period	0.00%		3	36	-$188,500.00
5				4	48	-$168,000.00
6	Monthly Payment	$ 1,708.33		5	60	-$147,500.00
7	Annual draw down plus fees	$ 20,500		6	72	-$127,000.00
8	Annual draw down	$ 20,000		7	84	-$106,500.00
9	Present Value	$ 250,000		8	96	-$86,000.00
10				9	108	-$65,500.00
11				10	120	-$45,000.00
12				11	132	-$24,500.00
13				12	144	-$4,000.00
14				13	156
15				14	168
16				15	180
17				16	192
18				17	204
19				18	216
20				19	228
21				20	240
22
23
24
25
26
27
28
Sheet1

Cell Formulas
Range	Formula
E2:E21	E2	=D2*12
F2:F21	F2	=IF(FV($B$4,E2,-$B$6,$B$9)<0,FV($B$4,E2,-$B$6,$B$9),"")
B4	B4	=B2/12
B6	B6	=B7/12
B7	B7	=B8+500

 

[DanteAmor]

With conditional formatting.
You fill the cell with white color and the letter also white:

Dante Amor
	A	B	C	D	E	F
1				Year	Periods	FV
2				1	12	-$229,500.00
3	Assumed growth	5.00%		2	24	-$209,000.00
4	Rate per period	0		3	36	-$188,500.00
5				4	48	-$168,000.00
6	Monthly Payment	1708.333333		5	60	-$147,500.00
7	Annual draw down plus fees	20500		6	72	-$127,000.00
8	Annual draw down	$20,000		7	84	-$106,500.00
9	Present Value	$250,000		8	96	-$86,000.00
10				9	108	-$65,500.00
11				10	120	-$45,000.00
12				11	132	-$24,500.00
13				12	144	-$4,000.00
14				13	156	$16,500.00
15				14	168	$37,000.00
16				15	180	$57,500.00
17				16	192	$78,000.00
18				17	204	$98,500.00
19				18	216	$119,000.00
20				19	228	$139,500.00
21				20	240	$160,000.00
22
Hoja3

Cell Formulas
Range	Formula
E2:E21	E2	=D2*12
F2:F21	F2	=FV($B$4,E2,-$B$6,$B$9)
B4	B4	=B2/12
B6	B6	=B7/12
B7	B7	=B8+500

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:F21	Expression	=$F2>=0	text	NO

Even the IF function in formula F is no longer needed.

 

[Phuoc]

Or try this:

Book1
	A	B	C	D	E	F
1				Year	Periods	FV
2				1	12	-229500
3	Assumed growth	5.00%		2	24	-209000
4	Rate per period	0		3	36	-188500
5				4	48	-168000
6	Monthly Payment	1708.333333		5	60	-147500
7	Annual draw down plus fees	20500		6	72	-127000
8	Annual draw down	$20,000		7	84	-106500
9	Present Value	$250,000		8	96	-86000
10				9	108	-65500
11				10	120	-45000
12				11	132	-24500
13				12	144	-4000
14
15
Sheet1

Cell Formulas
Range	Formula
D2:D101	D2	=LET(s,SEQUENCE(100),f,FV(B4,s*12,-B6,B9),IF(f<0,s,""))
E2:E101	E2	=IF(D2:D101="","",D2:D101*12)
F2:F101	F2	=IF(D2:D101="","",FV(B4,E2:E101,-B6,B9))
B4	B4	=B2/12
B6	B6	=B7/12
B7	B7	=B8+500
Dynamic array formulas.

 

[tigerzen]

Thanks Dante and Phuoc. I like the conditional formatting approach but it does leave empty table rows with the borders, I'd like to be able to get just clean cells after the calculation ends. Phuoc, when I try your formula and convert cells D to F into a table I get a Spill error message within the table.

 

[DanteAmor]

I like the conditional formatting approach but it does leave empty table rows with the borders, I'd like to be able to get just clean cells after the calculation ends.

Check this.
Delete the previous format and use the following:

Dante Amor
	A	B	C	D	E	F	G
1				Year	Periods	FV
2				1	12	-$229,500.00
3	Assumed growth	5.00%		2	24	-$209,000.00
4	Rate per period	0		3	36	-$188,500.00
5				4	48	-$168,000.00
6	Monthly Payment	1708.333333		5	60	-$147,500.00
7	Annual draw down plus fees	20500		6	72	-$127,000.00
8	Annual draw down	$20,000		7	84	-$106,500.00
9	Present Value	$250,000		8	96	-$86,000.00
10				9	108	-$65,500.00
11				10	120	-$45,000.00
12				11	132	-$24,500.00
13				12	144	-$4,000.00
14
15
16
17
18
19
20
21
22
Hoja3

Cell Formulas
Range	Formula
E2:E21	E2	=D2*12
F2:F21	F2	=FV($B$4,E2,-$B$6,$B$9)
B4	B4	=B2/12
B6	B6	=B7/12
B7	B7	=B8+500

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:F21	Expression	=$F2>=0	text	NO

Format:
Number / Custom:
;;;

Border / Style:
None
And delete each of the lines in the Border box

You will only notice the bottom right corner of the table, I hope that is not a problem.

 

[tigerzen]

Check this.
Delete the previous format and use the following:

Dante Amor
	A	B	C	D	E	F	G
1				Year	Periods	FV
2				1	12	-$229,500.00
3	Assumed growth	5.00%		2	24	-$209,000.00
4	Rate per period	0		3	36	-$188,500.00
5				4	48	-$168,000.00
6	Monthly Payment	1708.333333		5	60	-$147,500.00
7	Annual draw down plus fees	20500		6	72	-$127,000.00
8	Annual draw down	$20,000		7	84	-$106,500.00
9	Present Value	$250,000		8	96	-$86,000.00
10				9	108	-$65,500.00
11				10	120	-$45,000.00
12				11	132	-$24,500.00
13				12	144	-$4,000.00
14
15
16
17
18
19
20
21
22
Hoja3

Cell Formulas
Range	Formula
E2:E21	E2	=D2*12
F2:F21	F2	=FV($B$4,E2,-$B$6,$B$9)
B4	B4	=B2/12
B6	B6	=B7/12
B7	B7	=B8+500

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:F21	Expression	=$F2>=0	text	NO

Format:
Number / Custom:
;;;

View attachment 117892​

Border / Style:
None
And delete each of the lines in the Border box

View attachment 117894​

You will only notice the bottom right corner of the table, I hope that is not a problem.

View attachment 117895​

 

[tigerzen]

Thanks Dante.