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

#### Pablo78

##### New Member
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%

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
ABCDEFGH
1Total % Installed74.5%
2Value of activity65%15%10%5%5%
3Cable IDLength (mtrs)QTY Pulled (mtrs)Terminated End A (Y/N)Terminated End B (Y/N)Tested (Y/N)% Completed
4ABC-1234565050YYY100.0%
5ABC-1234577575YYN95.0%
6ABC-1234584343YNN80.0%
7ABC-1234598643NNN32.5%
8ABC-1234602222NNN65.0%
9
Sheet1
Cell Formulas
RangeFormula
F1F1=AVERAGE(\$G\$4:\$G\$8)
G4:G8G4=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))))

Replies
4
Views
307
Replies
2
Views
232
Replies
10
Views
369
Replies
7
Views
297
Replies
0
Views
108

1,203,071
Messages
6,053,375
Members
444,658
Latest member
lhollingsworth

### 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.

### Which adblocker are you using?

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

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