2

I'm trying to average a range but only if one condition is true.

   A B C
1: A 1 2
2: B 3 4
3: C 5 6
4: D 7 8
5: A 1 6
6: E 8 9
7: E 5 8

What I want is if the char I'm looking for is "A" then I want my average to produce something to the form of:

(1/2 + 1/6) /2
=AVERAGEIF(A:A, "="&SomeOtherVariable, B:B/C:C)

I want to avoid creating an extra column because the sheet that this data is on is taken from a google form and I am not sure how the form will take having a new column added to it.

  • What about using a second sheet to calculate the conditional average for the unique elements of column A instead of for each row, as the last will return redundant values? – Rubén Mar 24 '16 at 21:34
  • I just investigated and I can add a new column and that will not screw up new form data, but when new entries are added they wont have the correct function in that row. I think I should ask that question in another new question though. – alexander corley Mar 24 '16 at 22:07
  • Actually there are several questions in Web Applications about how to deal with form responses and calculations made on them. – Rubén Mar 24 '16 at 22:47
1

Short answer

Instead of AVERAGEIF use an array formula as AVERAGEIF requires a range as it's third parameter.

Explanation

The below formula could be used to calculate the average for each category without having to use an auxiliary column and as it use open ended references, it will not require to be modified when new form responses be submitted.

=ArrayFormula(
  QUERY(
    FILTER(
      {'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C},LEN('Sheet1'!A:A)
    ),
    "select Col1,AVG(Col2) group by Col1")
 )

Using the example source data provided by the OP, the result is the following:

+---+---+--------------+
|   | A |      B       |
+---+---+--------------+
| 1 |   | avg          |
| 2 | A | 0.3333333333 |
| 3 | B | 0.75         |
| 4 | C | 0.8333333333 |
| 5 | D | 0.875        |
| 6 | E | 0.7569444444 |
+---+---+--------------+
  • {'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C} creates an array with two columns, the first one is the category column, the second calculates the dividend of Column B divided by Column C.
  • The FILTER function is used to remove blank rows.
  • The QUERY function is used to calculate the average for each category in the first column.

References

  • That does exactly what I wanted it to, thanks. I haven't really used QUERY much, and i didn't even know you could build more than 1 column tables with ARRAYFORMULA. – alexander corley Mar 24 '16 at 23:14
1

NOTE: The following was valid for the revision 2

The error occurs because the comparison operator should be concatenated with SomeOtherVariable by using the ampersand & operator or CONCAT or CONCATENATE functions.

By the other hand, the third parameter of AVERAGEIF, when used, should be a range, it can't be an array.

Add a column to do the division, B:B/C:C, let say it's Column D, then the final formula will be

=AVERAGEIF(A:A, "="&SomeOtherVariable,D:D)
  • I edited to show the changes you suggested but I want to avoid an extra column because the sheet is generated off of a google form. meaning either the form won't know where to put the new entries. or every so often I would need to copy the formula into the new entries. What is the difference between a range and an array? – alexander corley Mar 24 '16 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.