Show Discrete Threshold Information as Thresholds are Reached

jski21

Board Regular
Joined
Jan 2, 2019
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1Project Completion Dashboard
2August 23, 20212020
3
4Performance LabelsAmount LabelsSpeedometer Pointer
5SeriesAmountsSeriesAmountsNeedleAmounts
6Needs Improvement20100Pointer25
7Average501010Thickness1
8Above Average201020Total174
9Excellent101030
10Total1001040
111050
121060
13Start Date: Jun 18, 20201070
146/18/202011/18/202025%108025% by Nov 18, 2020
154/18/202150%109050% by Apr 18, 2021
167/18/202180%10080% by Jul 18, 2021
174/18/202290%90% by Apr 18, 2022
184/18/2023100%100% by Apr 18, 2023
196/18/2027FinalAmt. to be drawn by Jun 18, 2027
20
21
22
23
24System 1
25Authorized Amount21,716,850
26Amt. Committed18,123,79383%
27Net Drawn8,107,15225%
28FY YTD Net Drawn8,104,52437%
29Available to Commit3,593,05717%
30Available to Draw13,609,69863%
31
32'Administrative Gap'(1,822,228)
33
34System 2
35Authorized Amount21,716,850
36Pre-Encumbered717,7293%
37Encumbered5,632,25526%
38Expended9,929,37946%
39Free Balance5,437,48725%
40Total Balance11,787,47154%
41
42Months remaining in total term69
43Monthly 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
RangeFormula
A2A2=TODAY()
Q6Q6=F27*100
Q8Q8=200-Q7-Q6
K10K10=SUM(K6:K9)
J13J13="Start Date: "&TEXT(J14,"mmm dd, yyyy")
M16M16=SUM(M6:M15)
K14K14=EDATE(J14,5)
K15K15=EDATE(K14,5)
K16K16=EDATE(K15,3)
K17K17=EDATE(K16,9)
K18K18=EDATE(K17,12)
K19K19=EDATE(J14,84)
P14:P18P14=TEXT(L14,"0%")& " by "&TEXT(K14, "mmm dd, yyyy")
P19P19="Amt. to be drawn by "&TEXT(K19, "mmm dd, yyyy")
F26,F28:F30F26=IF((E26/$E$25)=0,"-",(E26/$E$25))
E32E32=E27-E38
F36:F40F36=IF((E36/$E$35)=0,"-",(E36/$E$35))
C43C43=IF(AND(F27>=0,F27<=0.25),"Monthly spend to reach "&TEXT(L14,"0%")& " ("&DATEDIF(J14,K14,"M")&" mnths)","-")
C44C44=IF(AND(F27>0.25,F27<=0.5),"Monthly spend to reach "&TEXT(L15,"0%")& " ("&DATEDIF(J14,K15,"M")&" mnths)","-")
C45C45=IF(AND(F27>0.5,F27<=0.8),"Monthly spend to reach "&TEXT(L16,"0%")& " ("&DATEDIF(J14,K16,"M")&" mnths)","-")
C46C46=IF(AND(F27>0.8,F27<=0.9),"Monthly spend to reach "&TEXT(L17,"0%")& " ("&DATEDIF(J14,L17,"M")&" mnths)","-")
C47C47=IF(AND(F27>0.9,F27<=1),"Monthly spend to reach "&TEXT(L18,"0%")& " ("&DATEDIF(#REF!$A$2,K18,"M")&" mnths)","-")
C48C48=IF(AND(F28>0.9,F28<=1),"Monthly spend to final "&TEXT(L19,"0%")& " ("&DATEDIF(#REF!$A$2,K19,"M")&" mnths)","-")
F42F42=IF(K19<=$A$2,"-",DATEDIF($A$2,K19,"M"))
F43F43=IF((F27<=0.25),((E25*0.25)-E27)/(DATEDIF($A$2,DATEVALUE("Nov 18, 2020"),"m")),"-")
F44F44=IF((F27<=0.5),((E25*0.5)-E27)/(DATEDIF($A$2,DATEVALUE("Apr 18, 2021"),"m")),"-")
F45F45=IF((F27<=0.8),((E25*0.8)-E27)/(DATEDIF($A$2,DATEVALUE("Jul 18, 2021"),"m")),"-")
F46F46=IF((F27<=0.9),((E25*0.9)-E27)/(DATEDIF($A$2,DATEVALUE("Apr 18, 2022"),"m")),"-")
F47F47=IF((F27<=1),((E25*1)-E27)/(DATEDIF($A$2,DATEVALUE("Apr 18, 2023"),"m")),"-")
F48F48=IF((F28<=1),((E26*1)-E28)/(DATEDIF($A$2,DATEVALUE("Jun 18, 2027"),"m")),"-")
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top