The 63 MB Bundle and the Broken Status Sheet
A section must e-file a 480-page first appeal bundle before 5 p.m. today. The portal accepts PDF only, caps each file at 10 MB and each transaction at 20 files, and requires a searchable text layer; once a PDF is digitally signed it cannot be altered. The department scanner at 300 dpi grayscale yields 63 MB for the bundle; a rescan at 200 dpi would yield roughly 28 MB. Filenames must follow the pattern CEA_2025_014_part.pdf. Separately, a status sheet tracks 96 e-filings. The clerk's formula =COUNTIF($D$2:$D$97,"Filed") returns 58, but a manual count finds 63, because some cells hold 'Filed ' with trailing spaces and some are typed in mixed case. Two clerks share the upload folder, and yesterday one saved over the other's file, losing an hour of work. The section head is on leave and the deadline is not extendable.
1. Compute the minimum number of parts at each scanning resolution and recommend the approach that preserves legibility and the text layer.
Read the worked answer
At 300 dpi. 63 MB ÷ 10 MB = 6.3, so at least 7 files. If each part is kept under 9 MB to leave headroom, 63 ÷ 9 = 7 parts of about 69 pages each.
At 200 dpi. 63 × (200/300)² ≈ 28 MB, so 28 ÷ 9 = 3.1, i.e. 3 parts of about 160 pages each.
Recommendation. Rescan at 200 dpi with the text layer switched on, check that the smallest exhibit handwriting is legible at 100% zoom, and upload three parts. Fewer files means fewer chances of a wrong upload, and the 20-file cap is not the binding constraint. If any page fails the legibility check, rescan only those pages at 300 dpi as a separate part - never mix resolutions inside one part. Ensure the OCR layer is present: some portals reject image-only PDFs. Verify totals before upload: three parts, each under 10 MB, summing to about 28 MB, against the original 63 MB figure.
2. Diagnose the five-entry discrepancy in the status sheet and give a corrected formula plus a preventive measure.
Read the worked answer
Diagnosis. COUNTIF is already case-insensitive, so 'FILED' and 'Filed' both count; mixed case cannot explain the gap. The five missing entries are the trailing-space cells: the text 'Filed ' is not equal to 'Filed'.
Fixes.
- Clean the column: put =TRIM(D2) in a helper column, copy and paste values back, then use =COUNTIF($D$2:$D$97,"Filed").
- Count without cleaning: =SUMPRODUCT(--(TRIM($D$2:$D$97)="Filed")).
What not to do. Do not use "Filed*" as a wildcard: it will also count 'Filed and returned' or 'Filed - defect cured', which are different statuses, and it hides the real data-entry problem.
Prevention. Put a data-validation dropdown on the status column with the permitted statuses only, so free text cannot be typed in. Reconcile the corrected count of 63 against the portal acknowledgement receipts the same day; the acknowledgements, not the spreadsheet, are the record of what was actually filed.
3. Set out the steps that stop the overwriting problem and get the bundle uploaded by 5 p.m.
Read the worked answer
Procedure for the afternoon.
- Unique filenames before saving: add date, time and initials (…_20250430_1542_RK) at working stage; rename the final upload set to the mandated pattern only once it is final.
- Keep the final set in a read-only 'FINAL-UPLOAD' sub-folder that only the section head can write to; the second clerk prepares, the first uploads - one uploader at a time.
- Record a checksum (SHA-256) of each part in the register before upload, so any later corruption can be proved rather than argued about.
- Upload one part, download it, compare, then continue - rather than uploading all three and discovering a bad part last.
- Do not sign until the part is final: a signed PDF cannot be altered, and any post-signature correction means a fresh signature and a fresh upload.
- Save the portal acknowledgement and a screenshot with date and time on the case file.
Timing. Final set ready by 3 p.m., upload between 3.30 and 4 p.m., leaving an hour for re-upload if a part is rejected. The lost hour yesterday is the reason for the buffer.