Averaging a set of percentages between certain important levels I have a spreadsheet, called "To Do", with set of percentages in column E and importance levels in column A:



On another spreadsheet, I'm entering a formula to calculate the average of these percentages, but only those between certain importance levels:



See it on googledocs



This is the formula in question, indented for easier reading:

=TO_PERCENT(
     DIVIDE(
         SUMIF('To Do'!A3:A, "&lt;2000", 'To Do'!E3:E) 
       - SUMIF('To Do'!A3:A, "&lt;=1000", 'To Do'!E3:E), 
         MAX(
             COUNTIF('To Do'!A3:A, "&lt;2000") 
           - COUNTIF('To Do'!A3:A, "&lt;=1000"), 
             1
         )
     )
 )


Is there any better way to do this?

I originally wanted to use SUMIFS and COUNTIFS, but it said:


  error: Unknown function name
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 SUMIFS and 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 [Priority] values, "&lt;2000" and "&lt;=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 "&lt;2000" you'll have "&lt;" &amp; A$2, and instead of "&lt;=1000" you'll have "&lt;=" &amp; A$1.


Lastly, I find

MAX(COUNTIF('To Do'!A3:A, "&lt;2000") - COUNTIF('To Do'!A3:A, "&lt;=1000"), 1)


is easier to read as

MAX(1, COUNTIF('To Do'!A3:A, "&lt;2000") - COUNTIF('To Do'!A3:A, "&lt;=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 SUMIF / COUNTIF can do the trick: you only account for rows with a given [PriorityCode] value.

That's actually a "smell": your [Priority] values currently encode two values: the priority group, and the priority level, within that group.