CODEWITHSUNDEEP
excelexcel-formula
user3221570
user3221570

Reputation: 41

How to get average of only certain rows in a table

I have a table with 5 column and N rows. A row is added when a team changes size.

Column A is "Team Size at Start"
Column B is "Team Size at End"
Column C is "Velocity in Sprint N"
Column D is "Velocity in Sprint N+1"
Column E is "Velocity in Sprint N+2"

Row | A | B  | C  | D  | E (end row)
1   | 5 | 6  | 20 | 15 | 25 (end row)
2   | 4 | 7  | 10 | 8  | 15 (end row)
3   | 5 | 6  | 20 | 15 | 25 (end row)
4   | 4 | 7  | 15 | 12 | 18 (end row)
5   | 9 | 11 | 25 | 20 | 30 (end row)

I'm looking for a way to calculate the AVERAGE of column C, the AVERAGE of column D, and the AVERAGE of column E, but only when the values of columns A and B match in a row.

For example, I would want:

Averages for C, D, and E for rows 1 and 3, because Col A = Col B.
Averages for C, D, and E for rows 2 and 4, because Col A = Col B.
Averages for C, D, and E for row 5, because no other rows match Col A and B.

Upvotes: 0

Views: 4429

Answers (3)

Tarik
Tarik

Reputation: 11209

Create a pivot table with column A and B as row headers, the rest of the columns as values aggregated as average. You may want to select the classic view for the pivot table.

Upvotes: 0

Calum
Calum

Reputation: 2130

if:

column F becomes the average of column C,

column G becomes the average of column D,

column H becomes the average of column E,

paste this formula in F1 then copy it to all rows in F,G,H

=AVERAGEIFS(C:C,$A:$A,"="&$A1,$B:$B,"="&$B1)

Upvotes: 1

user4039065
user4039065

Reputation:

This seems like a fairly straightforward implementation of the AVERAGEIFS function if it can be reasonably assumed that you have Excel version 2007 or higher.

      AVERAGEIFS two column match

The formula in G1 is,

=IF(AND(COUNTIF($A$1:$A1, $A1)=COUNTIF($A:$A, $A1), COUNTIF($B$1:$B1, $B1)=COUNTIF($B:$B, $B1)), AVERAGEIFS(C:C,$A:$A, $A1,$B:$B, $B1), "")

Fill both right and down. I've added a condition where only the last occurrence of the matches displays the average. Remove that portion of it doesn't suit your needs.

Upvotes: 1

Related Questions