Calculating monthly averages

[LeadRush]

I have a sheet with three columns
Column A contains names.
Column B contains a date.
Column C contains a score.

I need to be able to calculate for each name in column A their overall average and their monthly average.

Is there a way to have Excel extract the unique names from column A, and then calculate the averages?

Thanks in advance

 

[NeonRedSharpie]

Sounds like you should look into using a pivot table. The monthly average might need a helper column, but you can add calculated fields into the pivot table once it's made. You can also look into a custom macro, but I think pivot tables are going to be what you need.

 

[ExcelMercy]

Use a column for your names:


=AVERAGEIF($A$2:$A$13,F2,$B$2:$B$13)

=AVERAGEIF($A$2:$A$13,F2,$C$2:$C$13)

Excel 2010

	A	B	C	D	E	F	G	H
1	Name	Date	Score			Name	Avg Date	Avg Score
2	Jimmy	6/21/2012	35			Jimmy	7/12/2001	47.66667
3	Jon	4/23/2014	25			Jon	1/15/2014	24.33333
4	Smith	2/5/2012	10			Smith	8/9/2004	23.33333
5	Bill	1/19/1999	5			Bill	1/20/2004	43.33333
6	Jimmy	9/8/1978	13
7	Jon	7/9/2014	25
8	Smith	1/1/1989	5
9	Bill	3/7/1998	78
10	Jimmy	6/7/2013	95
11	Jon	4/19/2013	23
12	Smith	9/20/2012	55
13	Bill	12/7/2014	47

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

 

[LeadRush]

Use a column for your names:


=AVERAGEIF($A$2:$A$13,F2,$B$2:$B$13)

=AVERAGEIF($A$2:$A$13,F2,$C$2:$C$13)

Excel 2010

	A	B	C	D	E	F	G	H
1	Name	Date	Score			Name	Avg Date	Avg Score
2	Jimmy	6/21/2012	35			Jimmy	7/12/2001	47.66667
3	Jon	4/23/2014	25			Jon	1/15/2014	24.33333
4	Smith	2/5/2012	10			Smith	8/9/2004	23.33333
5	Bill	1/19/1999	5			Bill	1/20/2004	43.33333
6	Jimmy	9/8/1978	13
7	Jon	7/9/2014	25
8	Smith	1/1/1989	5
9	Bill	3/7/1998	78
10	Jimmy	6/7/2013	95
11	Jon	4/19/2013	23
12	Smith	9/20/2012	55
13	Bill	12/7/2014	47

<tbody>
</tbody>

Sheet5​

Thanks, but I don't need an average for dates.
I want Column G to have a month, and then H will have the average for each agent for that month

 

[ExcelMercy]

For H:

January:
=AVERAGEIFS($C$2:$C$17,$A$2:$A$17,F2,$B$2:$B$17,">=1/1/2014",$B$2:$B$17,"<=1/31/2014")

February:
=AVERAGEIFS($C$2:$C$17,$A$2:$A$17,F7,$B$2:$B$17,">=2/1/2014",$B$2:$B$17,"<=2/28/2014")


Excel 2010

	A	B	C	D	E	F	G	H
1	Name	Date	Score			Name	Month	Avg Score
2	Jimmy	1/2/2014	35			Jimmy	January	24
3	Jon	1/3/2014	25			Jon	January	25
4	Smith	1/1/2014	10			Smith	January	7.5
5	Bill	1/4/2014	5			Bill	January	5
6	Jimmy	1/8/2014	13
7	Jon	1/9/2014	25			Jimmy	February	69
8	Smith	1/2/2014	5			Jon	February	22
9	Bill	2/7/2014	78			Smith	February	35
10	Jimmy	2/7/2014	95			Bill	February	71.33333
11	Jon	2/3/2014	23
12	Smith	2/4/2014	55
13	Bill	2/7/2014	47
14	Jimmy	2/8/2014	43
15	Jon	2/12/2014	21
16	Smith	2/14/2014	15
17	Bill	2/17/2014	89

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​