Google Sheets – Calculate Conditional Averages with Functions

google sheets

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.

Best Answer

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

Related Solutions

Google Sheets – Limit the AVERAGE Function

In a google spreadsheet, something like this should work:

=average(array_constrain(filter(B:B, A:A=1),10,1))

or alternatively:

=average(query(A:B, "Select B where A =1 limit 10",0))

Google Sheets – How to Use IF, AND Conditional Formatting

in this conditional-formatting-scenario is =ArrayFormula() kind of not necessary.
to hide #N/A you can use =IFERROR()
to avoid #N/A you can use =ISNUMBER =ISDATE =ISBLANK =ISTEXT etc.
and this custom formula is what you seek:

=IF( AND(ISNUMBER(J1:J);
         NOT(ISBLANK(L1:L))); COUNTIFS(J1:J; ">0"; 
                                       K1:K; ">"&DATE(0;1;1)))

it checks if J has number
and L is not blank
and then it checks if J>0
and K >1st january of year 0.

be sure you have right formatting otherwise it wont work:

update 1:

https://docs.google.com/spreadsheets/d/

update 2:

https://docs.google.com/spreadsheets/d/ where custom formula is:

=IF( AND(ISNUMBER(K2), 
         ISBLANK(M2), 
         ISDATE(L2)), COUNTIFS(K2, ">0", 
                               L2, ">"&DATE(0,1,1)))