Average Function from a list

[Infine]

Hello,

So this might be very easy, it is just that I do not know the function;

Column A:
Names

Column B:
Numbers


How do I take the average for each without making a pivot table etc? The table looks like this:


Name1 2
Name2 3
Name3 5
Name44 6
Name2 6
Name3 5
Name3 2
Name32 1
etc...

So I want it to sum All "NameX" which is the same and take the average of it and outcome shall be:

Name1 2
Name2 (3+6)/2
Name3 (5+5+2)/3


Anyone knows the function?

 

[Joe4]

You can use the AVERAGEIF function.
See: How to use the Excel AVERAGEIF function | Exceljet

 

[Infine]

I do not want to type "Name1" manually. How exactly should the function looks like? So I think something of FREQUENCY and SUM coulmn B which name is equal...

However, the NAMES are not static. They change so I want it to be dynamic..

 

[Infine]

Is this possible (I need help with the functions though):

Type out all unique names from Column A to Column C
=UNIQUE (array, [by_col], [exactly_once])

UNIQUE does not exist. (See side: How to use the Excel AVERAGEIF function | Exceljet )

Then =AVERAGEIF (columnB;ColumnC1)

Then I make 2 functions?

 

[Infine]

I have solved it by doing:

Take all names, copy, paste in new Column. Remove all doublets. Then AverageIF which match it. However, I want the first step be included in the code and be dynamic...

 

[Joe4]

Take all names, copy, paste in new Column. Remove all doublets. Then AverageIF which match it. However, I want the first step be included in the code and be dynamic...

Try using the Macro Recorder and record yourself doing this to get a lot of the code that you need.
If you need help cleaning it up afterwards, post what you have and we can help you with that.

 

[Infine]

I don't want a makro.. I want a function which looks like "=UNIQUE", However, my Excel version from work does not include this function. At the moment I did it manually but it is in a Static. I wish I could have it dynamic which updates the numbers everyday as the names adds.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Names	Numbers
2	Eden	1			Eden	4
3	High Peak	2			High Peak	2.5
4	High Peak	3			Bradford	12
5	Bradford	4			Staffordshire Moorlands	5
6	Staffordshire Moorlands	5			West Somerset	6
7	West Somerset	6			Shropshire	8
8	Eden	7			County Durham	12.5
9	Shropshire	8			Calderdale	10
10	County Durham	9			Northumberland	11
11	Calderdale	10			Rossendale	15.5
12	Northumberland	11			Oldham	13
13	Rossendale	12			Derbyshire Dales	14
14	Oldham	13			Kirklees	18
15	Derbyshire Dales	14
16	Bradford	15
17	County Durham	16
18	Bradford	17
19	Kirklees	18
20	Rossendale	19
21
22
Result

Cell Formulas
Range	Formula
E2:E17	E2	=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$100,E$1:E1,0)))/($A$2:$A$100<>""),1)),"")
F2:F17	F2	=IF(E2="","",AVERAGEIF(A:A,E2,B:B))

 

[Joe4]

I don't want a makro.

OK, your choice of words through me for a loop there.

However, I want the first step be included in the code and be dynamic...

To an Excel programmer, the word "code" implies VBA/Macros.
You wouldn't use the word "code" to describe Excel formulas.

In any event, it looks like Fluff gave you a formulaic solution that hopefully works for you.