Averaging cells in one column, based on another

[alarai]

Hello everyone,

I have multiple CSV files, which look something like this
34 8800
30 10500
30 10900
30 8000
31 7600
31 8000
34 8400
33 8500
34 8800
32 8300
35 9100
35 9000
39 9900
37 9200
34 8400
36 8900
36 9300


Where the first column is size of an ArrayList, and the second one is time needed to perform an operation on the ArrayList.
What I need to do is to have all the values from first column that are same merge, and create an average of the values from second column that match them.
Something that would look like this


30 9800
31 7800
32 8300
33 8500
34 8600
35 9050
36 9100
37 9200
39 9900


What I've been able to come up with so far is using something like this =AVERAGEIFS(B:B;A:A;">X";A:A;"<=Y") where it would average the cells in the interval (X,Y> , but if I want to do what I described using this method I would have to manually type in the interval for each individual value of first column which is something I'd like to avoid if possible.
Is there a function that would do all that for me automatically?


Thanks in advance!

[JohnTopley]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[etaf]

can you use a pivot table
OR
does this have to stay in CSV Format?

See attached

[Limor_OP]

Hi
How about that?
D2 and down:
=IFERROR(INDEX($A$1:$A$17,AGGREGATE(15,6,ROW($A$1:$A$17)/(COUNTIF($D$1:D1,$A$1:$A$17)=0),1)),"")

E2 and down:
=+AVERAGEIF(A1:A17,D2,B1:B17)

[alarai]

can you use a pivot table
OR
does this have to stay in CSV Format?

See attached

Thanks for the answer. Yes I can use pivot table I just didnt realize such thing even existed even after searching for quite some time. I am going to try and set it up, thank you very much!

[Limor_OP]

If you want column D to be sorted in ascending way use this
D2 and down -
=IFERROR(AGGREGATE(15,6,($A$1:$A$17)/(ROW($A$1:$A$17)=MATCH($A$1:$A$17,$A$1:$A$17,0)),ROWS($A$1:A1)),"")

[alarai]

Hi
How about that?
D2 and down:
=IFERROR(INDEX($A$1:$A$17,AGGREGATE(15,6,ROW($A$1:$A$17)/(COUNTIF($D$1:D1,$A$1:$A$17)=0),1)),"")

E2 and down:
=+AVERAGEIF(A1:A17,D2,B1:B17)

Thank you very much this is exactly what I was looking for!
Just one last question.
In the second function I can replace the range with A:A and B:B, but when I do the same thing with the first function it takes quite some time to calculate all the rows.
However if I type in the range manually it happens instantly, even if the last cell is on row 25000+. Is it possible to define the range from A1 cell to the last non blank cell in the row?

[Limor_OP]

My Advice: Dont take the whole column into calculation. Define a range that will take a significant spare cells.

[alarai]

Alright, I will follow your advice then! I will now mark the thread as solved. Thanks belinda and everyone else!