#Div/0 error when cell value decrease

[nevernine99]

In D7, When I increase value to 10 years or more. (Max is 40 yrs) it's automatically calculated in cells.
But when I decrease value to 5, I see #Div/0 error in cells. How do I leave cell blank if cell has #DIV/0 value?
I was trying with IFERROR function, but it doesn't work for me.

see attached XL2BB here. Thank you so much for your help.

Value based on year.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Adjustable Rate Mortgage (ARM) Calculator
2
3						Amortization Schedule									[42]
4		Loan Information				No.	Payment Date	Interest Rate	Payment Due	Extra Payment	Interest	Principal	Balance	Total Interest	Total Principal		Year	Average % Rate	Extra Payment	Interest	Principal	Balance
5								2.901%	($596,000.00)				$ 596,000
6			Loan amount	596,000.00		1	1-Jan-20	2.901%	5,727.80	100.00	1,440.78	4,387.02	591,612.98	1,440.78	4,387.02		2020	2.625%	100.00	15071.17	52921.89	543078.11
7			Term (years)	10		2	1-Feb-20	2.901%	5,727.80		1,430.18	4,297.62	587,315.36	2,870.96	8,684.64		2021	3.417%	100.00	17809.15	52547.44	490530.67
8			Starting interest rate	2.901%		3	1-Mar-20	2.901%	5,727.80		1,419.79	4,308.01	583,007.35	4,290.75	12,992.65		2022	2.583%	100.00	12085.59	56027.90	434502.77
9			First payment date	1-Jan-20		4	1-Apr-20	2.901%	5,727.80		1,409.37	4,318.43	578,688.92	5,700.12	17,311.08		2023	2.000%	0.00	8156.53	58413.35	376089.42
10						5	1-May-20	2.901%	5,727.80		1,398.93	4,328.87	574,360.05	7,099.05	21,639.95		2024	2.000%	0.00	6977.49	59592.39	316497.03
11			Starting monthly payment	5,727.80		6	1-Jun-20	3.000%	5,752.94		1,435.90	4,317.04	570,043.01	8,534.95	25,956.99		2025	2.000%	0.00	5774.66	60795.22	255701.81
12				.		7	1-Jul-20	3.000%	5,752.94		1,425.11	4,327.83	565,715.18	9,960.06	30,284.82		2026	2.000%	0.00	4547.54	62022.34	193679.47
13		Rate Adjustments				8	1-Aug-20	3.000%	5,752.94		1,414.29	4,338.65	561,376.53	11,374.35	34,623.47		2027	2.000%	0.00	3295.65	63274.23	130405.24
14			Rate	As Of …		9	1-Sep-20	2.000%	5,498.81		935.63	4,563.18	556,813.35	12,309.98	39,186.65		2028	2.000%	100.00	2017.87	64652.01	65753.23
15		Start Rate	2.901%	Jan-01-2020		10	1-Oct-20	2.000%	5,498.81		928.02	4,570.79	552,242.56	13,238.00	43,757.44		2029	2.000%	0.00	713.56	65753.23	0.00
16		New Rates	3.000%	Jun-01-2020		11	1-Nov-20	2.000%	5,498.81		920.40	4,578.41	547,664.15	14,158.40	48,335.85		2030	#DIV/0!	0.00	0.00	0.00
17			2.000%	Sep-01-2020		12	1-Dec-20	2.000%	5,498.81		912.77	4,586.04	543,078.11	15,071.17	52,921.89			#DIV/0!	0.00	0.00	0.00
18			4.000%	Jan-01-2021	{42}	13	1-Jan-21	4.000%	5,996.11		1,810.26	4,185.85	538,892.26	16,881.43	57,107.74			#DIV/0!	0.00	0.00	0.00
19			3.000%	Jun-01-2021		14	1-Feb-21	4.000%	5,996.11	100.00	1,796.31	4,299.80	534,592.46	18,677.74	61,407.54			#DIV/0!	0.00	0.00	0.00
20			3.000%	Apr-01-2022		15	1-Mar-21	4.000%	5,996.11		1,781.97	4,214.14	530,378.32	20,459.71	65,621.68			#DIV/0!	0.00	0.00	0.00
21			2.000%	Aug-01-2022		16	1-Apr-21	4.000%	5,996.11		1,767.93	4,228.18	526,150.14	22,227.64	69,849.86			#DIV/0!	0.00	0.00	0.00
22						17	1-May-21	4.000%	5,996.11		1,753.83	4,242.28	521,907.86	23,981.47	74,092.14			#DIV/0!	0.00	0.00	0.00
23						18	1-Jun-21	3.000%	5,753.72		1,304.77	4,448.95	517,458.91	25,286.24	78,541.09			#DIV/0!	0.00	0.00	0.00
24		Summary				19	1-Jul-21	3.000%	5,753.72		1,293.65	4,460.07	512,998.84	26,579.89	83,001.16			#DIV/0!	0.00	0.00	0.00
25			Est. Max monthly payment	5,996.11		20	1-Aug-21	3.000%	5,753.72		1,282.50	4,471.22	508,527.62	27,862.39	87,472.38			#DIV/0!	0.00	0.00	0.00
26			Total payments	672,449.21		21	1-Sep-21	3.000%	5,753.72		1,271.32	4,482.40	504,045.22	29,133.71	91,954.78			#DIV/0!	0.00	0.00	0.00
27			Total interest	76,449.21		22	1-Oct-21	3.000%	5,753.72		1,260.11	4,493.61	499,551.61	30,393.82	96,448.39			#DIV/0!	0.00	0.00	0.00
28			Internal Rate of Return	2.45%		23	1-Nov-21	3.000%	5,753.72		1,248.88	4,504.84	495,046.77	31,642.70	100,953.23			#DIV/0!	0.00	0.00	0.00
29						24	1-Dec-21	3.000%	5,753.72		1,237.62	4,516.10	490,530.67	32,880.32	105,469.33			#DIV/0!	0.00	0.00	0.00
30						25	1-Jan-22	3.000%	5,753.72		1,226.33	4,527.39	486,003.28	34,106.65	109,996.72			#DIV/0!	0.00	0.00	0.00
Loan Calc

Cell Formulas
Range	Formula
H5	H5	=D8
I5	I5	=-D6
M5	M5	=$D$6
F6:F30	F6	=IF(F5>=($D$7*12),"",F5+1)
G6:G30	G6	=IF(F6="","",DATE(YEAR($D$9),MONTH($D$9)+(F6-1),DAY($D$9)))
H6:H30	H6	=IF(ISNUMBER(G6),INDEX($C$15:$C$22,MATCH(G6,$D$15:$D$22,1)),"")
I6:I30	I6	=IF(F6="","",MIN(ROUND(IF(F6=1,$D$11,IF(H6=H5,I5,-PMT(H6/12,($D$7*12)-F6+1,M5))),2),M5+ROUND(H6/12*M5,2)))
K6:K30	K6	=IF(F6="","",ROUND(H6/12*M5,2))
L6:L30	L6	=IF(F6="","",I6-K6+J6)
M6:M30	M6	=IF(F6="","",M5-L6)
N6:N30	N6	=IF(F6="","",SUM(K$6:K6))
O6:O30	O6	=IF(F6="","",SUM(L$6:L6))
Q6	Q6	=YEAR(D9)
R6:R30	R6	=AVERAGEIFS($H$5:$H$484,$G$5:$G$484,">="&DATE(Q6,1,1),$G$5:$G$484,"<="&DATE(Q6,12,31))
S6:S30	S6	=IFERROR(SUMIFS(J$5:J$484,$G$5:$G$484,">="&DATE($Q6,1,1),$G$5:$G$484,"<="&DATE($Q6,12,31)),"")
T6:U30	T6	=IFERROR(SUMIFS(K$6:K$485,$G$6:$G$485,">="&DATE($Q6,1,1),$G$6:$G$485,"<="&DATE($Q6,12,31))," ")
V6:V30	V6	=IFERROR(VLOOKUP(DATEVALUE("December 1, " & $Q6),$G$4:$O$485,7,0),"")
Q7:Q30	Q7	=IFERROR(IF(Q6+1>(Q$6+D$7),"",Q6+1),"")
D11	D11	=ROUND(-PMT(D8/12,($D$7*12),$D$6),2)
D12	D12	=IF(($D$7*12)>480,"ERROR: >480 payments",".")
C15	C15	=D8
D15	D15	=$D$9
D25	D25	=MAX(I6:I485)
D26	D26	=SUM(K6:K485)+SUM(L6:L485)
D27	D27	=SUM(K5:K485)
D28	D28	=IRR(I5:I485,1%)*12

Cells with Data Validation
Cell	Allow	Criteria
D15	Any value

 

[Fluff]

How about

=IFERROR(AVERAGEIFS($H$5:$H$484,$G$5:$G$484,">="&DATE(Q16,1,1),$G$5:$G$484,"<="&DATE(Q16,12,31)),"")

 

[nevernine99]

The above formula works very well in column R. But when I used formulas with IFERROR in Column S, T, U, V.
I am getting "0.00" instead of blank cell.

 

[Fluff]

Firstly the formula in cols T & U have " " rather than ""
That said the sumifs will only return an error if there are error values in the sum range.

 

[nevernine99]

@Fluff,
I tried both way for "" & " " but I got same result.

 

[Fluff]

Yes, but as I said sumifs will only return an error if there are error in the range you are summing.
As col J looks to be values rather than a formula, you will never get an error.

 

[nevernine99]

I used following formula to match if column Q has value, and its works great.

=IF(Q31="","",IFERROR(SUMIFS(K$6:K$485,$G$6:$G$485,">="&DATE($Q31,1,1),$G$6:$G$485,"<="&DATE($Q31,12,31)),""))

Thank you for your help.

 

[Fluff]

Glad you sorted it & thanks for the feedback.