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.
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")
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))
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.