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, "<2000", 'To Do'!E3:E)
- SUMIF('To Do'!A3:A, "<=1000", 'To Do'!E3:E),
MAX(
COUNTIF('To Do'!A3:A, "<2000")
- COUNTIF('To Do'!A3:A, "<=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

• Ask Google to add SUMIFS() / COUNTIFS() / AVERAGEIF() ? – Snowbody Jul 1 '14 at 15:04
• But seriously, there is a scripting language inside Google Spreadsheets that allows you to write your own worksheet functions, but I don't know anything about it. developers.google.com/apps-script/quickstart/macros – Snowbody Jul 1 '14 at 15:33
• This question is being discussed on meta – rolfl Jul 24 '14 at 2:39
• Averaging percentages sounds like a suspicious mathematical technique. Are you sure that it makes sense to do that? (We have no idea what problem you are really trying to solve, so we have no context with which to help you catch logic errors.) – 200_success Jul 24 '14 at 5:22

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 , but I know 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 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, "<2000" and "<=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 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.

• I guess you've found your mug now ;) – Mathieu Guindon Aug 31 '14 at 5:54
• A3:A means "All cells in the A column starting with the third-down", since I don't care about column headings. It's okay to hard-code my importance ranges in the same way it's okay for window managers to hard-code their layer levels (e.g. always-on-top = 10,000, modal = 5,000, normal = 0, etc.) since Immediate tasks will never have a value <0 or >=1000, etc. Somehow you didn't see that column A has all the priority levels, where 0 is highest and 9999 is lowest. – Supuhstar Aug 31 '14 at 17:43
• No, it's not ok, and yes I've seen what column A contains. You'll have 9 columns with 9 almost-identical formulas that only differ by what ranges in column A are being accounted for. What I'm saying is that if you had another column to hold the "priority groups" for each row, you could get what you need without having to subtract the "not in range" values - in fact there wouldn't be a need for a "range" at all. Just have a column that contains "High" for all such rows, and "Low" for all low-priority tasks, and so on: you're making this much harder than it needs to be. – Mathieu Guindon Aug 31 '14 at 17:53
• I didn't know about A3:A though, that's interesting. Doesn't work in Excel though. – Mathieu Guindon Aug 31 '14 at 17:55
• The range is there because these have finer priorities than just the groups. If I only needed 0 through 9, I'd only use 0 through 9. – Supuhstar Aug 31 '14 at 17:57

Use a pivot

Your approach works, and I'll give you that. I feel you are overcomplicating something that is, on the whole, quite simple. Instead of using numbers 1-10,000 to define priority, why not use a simple method like numbers {1,2,3,4,5,6,7,8,9} and just pivot them? Or maybe to make it more clear, normalize to something like {"1-Immediate", "2-Very High", "3-High", "4-Medium", "5-Low", "6-Very Low", "7-On Hold", "8-Impossibru", "9-Done"} ?

Edit: Mind you, percentages can still be used, it just seems very odd to aggregate aggregated values.