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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,236
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top