excel multiple average in single column

0
votes

I would like help with my excel spreadsheet. In one column I have product number in the second column there is execution time:
A B C
1 0:17
1 0:18
1 0:19 0:18
2 1:12
2 1:12
2 1:13 1:12:6
3 0:45 0:45
I would like to make an average for every product in the next column (column C). I have tried:

=IF(A1=A2;0;AVERAGE(IF(A:A=A1;B:B)))

but it fails miserably. So my question is how can I automatically select a range in A with the same number to make an average and display it at the end of that product?

EDIT:I use excel 2003.

Thank you for your help.

3
excel

3 Answers

0
votes

Your way will work but the formula needs to be "array entered". Better to use AVERAGEIF function like this in C1 copied down

=IF(A1=A2;"";AVERAGEIF(A$1:A1;A1;B$1:B1))

For Excel 2003 or earlier versions you can either use this "array formula" (essentially what you had originally)

=IF(A1=A2;"";AVERAGE(IF(A$1:A1=A1;B$1:B1)))

confirmed with CTRL+SHIFT+ENTER then copied down

....or use this non-array version

=IF(A1=A2;"";SUMIF(A$1:A1;A1;B$1:B1)/COUNTIF(A$1:A1;A1))

0
votes

Formula:

=IF(COUNTIF($A2:$A$65535,$A1)=0,SUMIF($A$1:$A1,$A1,$B$1:$B1)/COUNTIF($A$1:$A1,$A1),"")

Paste this formula into C1 and fill or copy down to the bottom of the data.

This formula also accounts for unsorted data.

-1
votes

You can use AVERAGEIF or AVERAGEIFS to include a condition or multiple conditions in average calculation.

First of All copy and paste column A in a new column ( Exa , Column F ) and then Remove duplicates.

=AVERAGEIF("A:A",F1,"B:B")