Show Discrete Threshold Information as Thresholds are Reached

[jski21]

Good morning Mr. Excel Team,

I need a second set of eyes on an issue I'm trying to work through. Need to track project spending along a set thresholds. If the threshold is not yet met, I'd like to have that line show with the necessary data. Once the threshold is reached, I'd like to have that line item disappear, leaving only the remaining thresholds showing that have yet to be reached.

Example: Thresholds are 25%, 50%, 80%, 90%, 100%. Each is assigned to a specific date deadline. There is also a final deadline as well. If spending on the project is currently at say 11%, all the lines for 25%, 50%, 80%, 90%, 100%, and final date would show. If spending moves to 37%, only lines 50%, 80%, 90%, 100%, and final date would show. Progression is as spending increases, the lower thresholds disappear revealing only the thresholds yet to be reached. I've set it to work by dates and percentages, but changing the percent to completion (yellow highlight) sometimes gives me a #NUM! error. Also can't seem to get the progression to work properly. The formulas reside in C43:F48 (highlighted in green).

Any guidance, instruction, or advice is appreciated. Thanks all.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Project Completion Dashboard
2	August 23, 2021								2020
3
4										Performance Labels			Amount Labels			Speedometer Pointer
5										Series	Amounts		Series	Amounts		Needle	Amounts
6										Needs Improvement	20		10	0		Pointer	25
7										Average	50		10	10		Thickness	1
8										Above Average	20		10	20		Total	174
9										Excellent	10		10	30
10										Total	100		10	40
11													10	50
12													10	60
13										Start Date: Jun 18, 2020			10	70
14										6/18/2020	11/18/2020	25%	10	80		25% by Nov 18, 2020
15											4/18/2021	50%	10	90		50% by Apr 18, 2021
16											7/18/2021	80%	100			80% by Jul 18, 2021
17											4/18/2022	90%				90% by Apr 18, 2022
18											4/18/2023	100%				100% by Apr 18, 2023
19											6/18/2027	Final				Amt. to be drawn by Jun 18, 2027
20
21
22
23
24			System 1
25			Authorized Amount		21,716,850
26			Amt. Committed		18,123,793	83%
27			Net Drawn		8,107,152	25%
28			FY YTD Net Drawn		8,104,524	37%
29			Available to Commit		3,593,057	17%
30			Available to Draw		13,609,698	63%
31
32			'Administrative Gap'		(1,822,228)
33
34			System 2
35			Authorized Amount		21,716,850
36			Pre-Encumbered		717,729	3%
37			Encumbered		5,632,255	26%
38			Expended		9,929,379	46%
39			Free Balance		5,437,487	25%
40			Total Balance		11,787,471	54%
41
42			Months remaining in total term			69
43			Monthly spend to reach 25% (5 mnths)			#NUM!
44			-			#NUM!
45			-			#NUM!
46			-			1,634,002
47			-			716,300
48			-			145,207
49
Dashboard

Cell Formulas
Range	Formula
A2	A2	=TODAY()
Q6	Q6	=F27*100
Q8	Q8	=200-Q7-Q6
K10	K10	=SUM(K6:K9)
J13	J13	="Start Date: "&TEXT(J14,"mmm dd, yyyy")
M16	M16	=SUM(M6:M15)
K14	K14	=EDATE(J14,5)
K15	K15	=EDATE(K14,5)
K16	K16	=EDATE(K15,3)
K17	K17	=EDATE(K16,9)
K18	K18	=EDATE(K17,12)
K19	K19	=EDATE(J14,84)
P14:P18	P14	=TEXT(L14,"0%")& " by "&TEXT(K14, "mmm dd, yyyy")
P19	P19	="Amt. to be drawn by "&TEXT(K19, "mmm dd, yyyy")
F26,F28:F30	F26	=IF((E26/$E$25)=0,"-",(E26/$E$25))
E32	E32	=E27-E38
F36:F40	F36	=IF((E36/$E$35)=0,"-",(E36/$E$35))
C43	C43	=IF(AND(F27>=0,F27<=0.25),"Monthly spend to reach "&TEXT(L14,"0%")& " ("&DATEDIF(J14,K14,"M")&" mnths)","-")
C44	C44	=IF(AND(F27>0.25,F27<=0.5),"Monthly spend to reach "&TEXT(L15,"0%")& " ("&DATEDIF(J14,K15,"M")&" mnths)","-")
C45	C45	=IF(AND(F27>0.5,F27<=0.8),"Monthly spend to reach "&TEXT(L16,"0%")& " ("&DATEDIF(J14,K16,"M")&" mnths)","-")
C46	C46	=IF(AND(F27>0.8,F27<=0.9),"Monthly spend to reach "&TEXT(L17,"0%")& " ("&DATEDIF(J14,L17,"M")&" mnths)","-")
C47	C47	=IF(AND(F27>0.9,F27<=1),"Monthly spend to reach "&TEXT(L18,"0%")& " ("&DATEDIF(#REF!$A$2,K18,"M")&" mnths)","-")
C48	C48	=IF(AND(F28>0.9,F28<=1),"Monthly spend to final "&TEXT(L19,"0%")& " ("&DATEDIF(#REF!$A$2,K19,"M")&" mnths)","-")
F42	F42	=IF(K19<=$A$2,"-",DATEDIF($A$2,K19,"M"))
F43	F43	=IF((F27<=0.25),((E25*0.25)-E27)/(DATEDIF($A$2,DATEVALUE("Nov 18, 2020"),"m")),"-")
F44	F44	=IF((F27<=0.5),((E25*0.5)-E27)/(DATEDIF($A$2,DATEVALUE("Apr 18, 2021"),"m")),"-")
F45	F45	=IF((F27<=0.8),((E25*0.8)-E27)/(DATEDIF($A$2,DATEVALUE("Jul 18, 2021"),"m")),"-")
F46	F46	=IF((F27<=0.9),((E25*0.9)-E27)/(DATEDIF($A$2,DATEVALUE("Apr 18, 2022"),"m")),"-")
F47	F47	=IF((F27<=1),((E25*1)-E27)/(DATEDIF($A$2,DATEVALUE("Apr 18, 2023"),"m")),"-")
F48	F48	=IF((F28<=1),((E26*1)-E28)/(DATEDIF($A$2,DATEVALUE("Jun 18, 2027"),"m")),"-")