As @200_success pointed out in his comment, averaging percentages is a suspicious mathematical technique.
Each task in the TODO list could have its own weight, or relative value - a number that represents a chunk of progress towards "done"; then you can calculate a percentage by adding up the relative weights of all completed tasks (or more accurately, of a value derived from that task's %completed and weight), and dividing by the sum of the weights of all tasks.
I haven't played much with google-sheets, but I know excel pretty well; the formula you've come up with is exactly the kind of formula I'd have used in Excel 2003, before
COUNTIFS were introduced in Excel 2007.
When I enter a formula in a spreadsheet, I want to be able to copy that formula over to the next cell, without having to modify it. This involves a number of principles:
- Don't hard-code cell references. In Excel I would have used names and/or tables - not sure google-sheets supports that, but in any case if none of that is supported you can still, and should, use absolute cell references - refer to
'To Do'!$A:$A and
'To Do'!$E:$E. Not sure what
A3:A refers to.
- Don't hard-code your variables. If each column is going to use a different set of
"<=1000" shouldn't be hard-coded. You can insert two rows above row 1, and put
1000 in row 1 and
2000 in row 2, so instead of
"<2000" you'll have
"<" & A$2, and instead of
"<=1000" you'll have
"<=" & A$1.
Lastly, I find
MAX(COUNTIF('To Do'!A3:A, "<2000") - COUNTIF('To Do'!A3:A, "<=1000"), 1)
is easier to read as
MAX(1, COUNTIF('To Do'!A3:A, "<2000") - COUNTIF('To Do'!A3:A, "<=1000"))
But the reason you're taking the
MAX here, is to avoid a division by zero; by dividing by 1 whenever that's the case, you're showing mathematically incorrect results.
In Excel I'd wrap the division with an
IFERROR, and return a string such as
"-" when I'm dividing by zero. This accurately reports "this category is irrelevant", rather than "this category is 100% done".
Another simple option would be to add a column in the TODO list, to identify the actual priority level (which you currently have as a wasted row between each group); then a simple
COUNTIF can do the trick: you only account for rows with a given
That's actually a "smell": your
[Priority] values currently encode two values: the priority group, and the priority level, within that group.