CODEWITHSUNDEEP
excel-2007
Sylvia
Sylvia

Reputation: 11

Excel: one column has duplicates of each value, I need to take averages of the corresponding two values from the other columns

Example: 

column A column B
A         1
A         2
B         2
B         2
C         1
C         1

I would somehow like to get the following result: 

column A column B
A        1.5
B        2
C        1

(which are averages of 1 and 2, 2 and 2 and 1 and 1) How do I achieve that? Thanks

Upvotes: 1

Views: 5083

Answers (2)

Sleette
Sleette

Reputation: 376

If you're using Excel 2007 or above, you can also use the shorter AVERAGEIF function:

=AVERAGEIF($A$1:$A:$6,D1,$B$1:$B$6)

Less typing, easier to read..

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33145

In D1:D3, type A, B, C. Then in E1, put this formula

=SUMIF($A$1:$A$6,D1,$B$1:$B$6)/COUNTIF($A$1:$A$6,D1)

and fill down to E3. If you want to replace the existing data, copy E1:E3 and paste-special-values over itself. Then delete A:C.

Alternatively, you can add headers to your data, say "Letter" and "Number". Then create a Pivot Table from your data. Put Letter in the rows section and Number in the Data section. Change your Data section from SUM to AVERAGE and you'll get the same result.

Upvotes: 0

Related Questions