Cable installation tracker based on letters and numbers to calculate a percentage complete

[Pablo78]

Hello all,

I am trying to create a table to track the progress of a cable installation. The cable length varies for each cable so I have quantified as follows. Pulling the cable would be valued at 65% of the installation, however I would also like to have the percentage based on the QTY pulled, for instance if 50m of a 100m cable run has been achieved, the total percentage completed on that part of the activity would be 32.5% of the total completion.

Cable terminations, I have valued at 15% each of the overall installation of a cable but would like to identify them with a Y/N scenario. Testing is allocated as 5% of the overall cable install and again I would like that to be identified with a Y/N.

I have been wracking my brain on how I can achieve this and have thought about having another tab that will draw the input data from the main tab to handle the calculations and give the overall % completed back in the main tab but so far I have had no luck. Can any of you kind people help me out?

				Total % Installed	75%
Value of activity	65%	15%	15%	5%
Cable ID	Length (mtrs)	QTY Pulled (mtrs)	Terminated End A (Y/N)	Terminated End B (Y/N)	Tested (Y/N)	% Completed
ABC-123456	50	50	Y	Y	Y	100%
ABC-123457	75	75	Y	Y	N	95%
ABC-123458	43	43	Y	N	N	80%
ABC-123459	86	43	N	N	N	34%
ABC-123460	22	22	N	N	N	65%

 

[RasGhul]

Hi Pablo,

See if this works with your larger data set, Note that I reduced "Terminated End A" to 10% & allocated 5% to Tested as per your goal to make up the 100%

Cable installation tracker_Pablo78.xlsx
	A	B	C	D	E	F	G	H
1					Total % Installed	74.5%
2	Value of activity	65%	15%	10%	5%	5%
3	Cable ID	Length (mtrs)	QTY Pulled (mtrs)	Terminated End A (Y/N)	Terminated End B (Y/N)	Tested (Y/N)	% Completed
4	ABC-123456	50	50	Y	Y	Y	100.0%
5	ABC-123457	75	75	Y	Y	N	95.0%
6	ABC-123458	43	43	Y	N	N	80.0%
7	ABC-123459	86	43	N	N	N	32.5%
8	ABC-123460	22	22	N	N	N	65.0%
9
Sheet1

Cell Formulas
Range	Formula
F1	F1	=AVERAGE($G$4:$G$8)
G4:G8	G4	=IF(((B4-C4)/B4)*$B$2>0,((B4-C4)/B4)*$B$2,IF(AND(D4="N",E4="N"),$B$2,IF(AND(D4="Y",E4="N"),SUM($B$2:$C$2),SUMPRODUCT((D4="Y")*$D$2+(E4="Y")*$E$2+(F4="Y")*$F$2)+SUM($B$2:$C$2))))