Negative Feeder Loss and Shared Excel Register
A junior engineer keeps the subdivision's daily energy register in one Excel workbook on a shared desktop. Columns are Date, Import (kWh), Export (kWh), Loss (kWh) and Loss (%). The loss formula is =D2/B2*100, where D is =B2-C2. For 12 May, Import is 48,000 kWh, Export is 51,200 kWh, Loss is −3,200 kWh and Loss % is −6.67. The monthly total cell uses =SUM(D2:D32) and comes out 1,240 kWh lower than the control-room total. One number has been typed with an apostrophe and is stored as text; another cell contains only a space. Two rows show #DIV/0!. A clerk has emailed the file to a contractor, and a second copy has been edited by two people; the two copies now differ. The register feeds the monthly feeder-loss report.
1. Is the negative loss necessarily a data-entry error? List the checks and the most likely legitimate explanation.
Read the worked answer
No. Export greater than import can be genuine on a feeder with net metering or rooftop solar, or it can be a measurement error. Checks in order: (1) confirm that the import and export readings come from the same meter and the same reading period; (2) check the meter multiplier and CT/PT ratio-applying a ratio twice is a common cause; (3) check whether the meter is configured as bidirectional and whether any net-metered consumer or solar plant is on the feeder; (4) compare with the previous three months and with the sum of consumer bills; (5) test the meter or install a check meter; (6) verify that kWh and kVArh columns have not been interchanged. If there is no embedded generation, the most likely explanation is a CT ratio or meter connection error, not a real export. Do not simply take the absolute value or swap the columns; that would hide a metering defect and could misstate the loss.
2. Give corrected Excel formulas for loss, loss percentage and the monthly total. Explain why SUM alone gave a low total.
Read the worked answer
Loss in D2: =IF(AND(ISNUMBER(B2),ISNUMBER(C2)),B2-C2,""). Loss % in E2: =IFERROR(D2/B2,"") or, to guard zero and text, =IF(AND(ISNUMBER(D2),ISNUMBER(B2),B2>0),D2/B2,""). Monthly total: =SUMPRODUCT(--ISNUMBER(D2:D32),D2:D32).
SUM gave a low total because one numeric value was stored as text (for example '3,200) and SUM ignores text; a space in another cell also made that row non-numeric. SUMPRODUCT with ISNUMBER adds only true numbers and ignores text. Also add data validation on B and C as decimal ≥0, conditional formatting for a negative loss beyond a set threshold, and replace merged cells with an Excel Table so formulas fill consistently.
3. Recommend controls for the shared workbook and the contractor copy.
Read the worked answer
Keep one controlled master on SharePoint or OneDrive with version history; do not use email attachments as the working copy. Give the contractor a read-only link or a locked copy, and require changes to be sent as a request, not made in a second file. Protect the formula columns and lock the sheet; leave only the input cells unlocked. Use an Excel Table with structured references, remove merged cells, and add a change log sheet with date, editor and reason. Reconcile the daily total with the meter register and with the monthly feeder report before submission. Take a daily backup or rely on version history, and restrict edit rights to named staff. If two people must edit, use co-authoring on the same file, not two copies. These controls also make the negative-loss exception auditable.