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.

Example of progress

The way it works is straightforward; suppose Start Row is 2 and End Row is 5

You can even get cute and put a data bar in that fills as annotation progresses. This simple trick helped me feel like I was getting somewhere with the annotation I was doing, and helped the team work together to get it completed in time.