Calculating monthly averages

LeadRush

New Member
Joined
Sep 8, 2014
Messages
2
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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

Board Regular
Joined
Aug 11, 2014
Messages
151
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
ABCDEFGH
1NameDateScoreNameAvg DateAvg Score
2Jimmy6/21/201235Jimmy7/12/200147.66667
3Jon4/23/201425Jon1/15/201424.33333
4Smith2/5/201210Smith8/9/200423.33333
5Bill1/19/19995Bill1/20/200443.33333
6Jimmy9/8/197813
7Jon7/9/201425
8Smith1/1/19895
9Bill3/7/199878
10Jimmy6/7/201395
11Jon4/19/201323
12Smith9/20/201255
13Bill12/7/201447

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

LeadRush

New Member
Joined
Sep 8, 2014
Messages
2
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
ABCDEFGH
1NameDateScoreNameAvg DateAvg Score
2Jimmy6/21/201235Jimmy7/12/200147.66667
3Jon4/23/201425Jon1/15/201424.33333
4Smith2/5/201210Smith8/9/200423.33333
5Bill1/19/19995Bill1/20/200443.33333
6Jimmy9/8/197813
7Jon7/9/201425
8Smith1/1/19895
9Bill3/7/199878
10Jimmy6/7/201395
11Jon4/19/201323
12Smith9/20/201255
13Bill12/7/201447

<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

Board Regular
Joined
Aug 11, 2014
Messages
151
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
ABCDEFGH
1NameDateScoreNameMonthAvg Score
2Jimmy1/2/201435JimmyJanuary24
3Jon1/3/201425JonJanuary25
4Smith1/1/201410SmithJanuary7.5
5Bill1/4/20145BillJanuary5
6Jimmy1/8/201413
7Jon1/9/201425JimmyFebruary69
8Smith1/2/20145JonFebruary22
9Bill2/7/201478SmithFebruary35
10Jimmy2/7/201495BillFebruary71.33333
11Jon2/3/201423
12Smith2/4/201455
13Bill2/7/201447
14Jimmy2/8/201443
15Jon2/12/201421
16Smith2/14/201415
17Bill2/17/201489

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,597
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top