# Excel Completion Count

excel
Published

June 13, 2020

I was recently running some simple, but tedious, annotation in Excel. While it’s not a good tool for complex annotation for a problem with a simple textual annotation where you can fit all the information to make a decision in a row it can be effective. However I needed a way to track progress across the team to make sure we finished on time, and see who needed help.

We had a blank column that was being filled in as the annotation progressed, and each person was working on some set of rows. To see progress I ended up using a formula like this:

``````=AVERAGE(IF(OFFSET(annotation,
[@[Start Row]]-1,                 0,
[@[End Row]] - [@[Start Row]] +1, 1
) = "",
0, 1))``````

Where annotation is the first cell of the column being annotated, and Start Row and End Row refer to the row numbers that are to be filled in.

• `annotation` is at the top of the column, say B1
• `OFFSET(annotation, [Start Row] - 1, 0, ...)` gets the range starting at row 1 + (2-1), that is B2
• `OFFSET(annotation, ..., [End Row] - [Start Row] + 1, 1)` gets the rectangle of length (5-2) + 1 = 4 and width 1 (so contains 4 cells, B2, B3, B4 and B5).
• `IF(OFFSET(...) = "", 0, 1)` then turns this into a vector where we get 0 if empty (no annotation) and 1 if filled (some annotation)
• `AVERAGE(IF(...))` then gives the proportion of cells filled, as a percentage between 0% (no annotation) and 100% (all filled)