Calculating monthly averages

L

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
 
Sort by date Sort by votes
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.
 
Upvote 0
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
NameNameAvg DateAvg Score
JimmyJimmy
JonJon
SmithSmith
BillBill
Jimmy
Jon
Smith
Bill
Jimmy
Jon
Smith
Bill

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]
[TD="align: right"]Score[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6/21/2012[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7/12/2001[/TD]
[TD="align: right"]47.66667[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4/23/2014[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/15/2014[/TD]
[TD="align: right"]24.33333[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/5/2012[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8/9/2004[/TD]
[TD="align: right"]23.33333[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/19/1999[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/20/2004[/TD]
[TD="align: right"]43.33333[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9/8/1978[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7/9/2014[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1/1/1989[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3/7/1998[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]6/7/2013[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4/19/2013[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]9/20/2012[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]12/7/2014[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​
 
Upvote 0
ExcelMercy said:
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
NameNameAvg DateAvg Score
JimmyJimmy
JonJon
SmithSmith
BillBill
Jimmy
Jon
Smith
Bill
Jimmy
Jon
Smith
Bill

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]
[TD="align: right"]Score[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6/21/2012[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7/12/2001[/TD]
[TD="align: right"]47.66667[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4/23/2014[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/15/2014[/TD]
[TD="align: right"]24.33333[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/5/2012[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8/9/2004[/TD]
[TD="align: right"]23.33333[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/19/1999[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/20/2004[/TD]
[TD="align: right"]43.33333[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9/8/1978[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7/9/2014[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1/1/1989[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3/7/1998[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]6/7/2013[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4/19/2013[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]9/20/2012[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]12/7/2014[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​
Click to expand...

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
 
Upvote 0
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
NameNameMonthAvg Score
JimmyJimmyJanuary
JonJonJanuary
SmithSmithJanuary
BillBillJanuary
Jimmy
JonJimmyFebruary
SmithJonFebruary
BillSmithFebruary
JimmyBillFebruary
Jon
Smith
Bill
Jimmy
Jon
Smith
Bill

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]
[TD="align: right"]Score[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]24[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1/3/2014[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]25[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7.5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/4/2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/8/2014[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1/9/2014[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]69[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2/7/2014[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]35[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2/7/2014[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]71.33333[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2/3/2014[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2/4/2014[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2/7/2014[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2/8/2014[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2/12/2014[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]2/14/2014[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]2/17/2014[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Get average balance of date range broken down by month
2
Replies
11
Views
283
Tetra201
Tetra201
Simon2001
  • Solved
Better Average Method
Replies
2
Views
114
Simon2001
Simon2001
J
  • Solved
Weighted Average Formulas
Replies
5
Views
141
Sam_D_Ben
Sam_D_Ben
C
  • Solved
Finding the average of the top 10 values
Replies
3
Views
113
Eric W
Eric W
J
  • Question Question
Employee Timesheet Pivot Table
Replies
2
Views
358
alansidman
alansidman

Share this page

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
Back
Top