Formula for Finding the Average Number of Calls per Month

C

carla7216

New Member
Joined
Aug 13, 2009
Messages
29
Hello Everyone,

I have a telephone record spreadsheet that I need to find the average number of calls per month and the average number of calls, per telephone number, each month. This spreadsheet consist of several telephone numbers and a range of dates.

The dates are listed as follows:

8/22/14
8/23/14

all the way to....

5/21/14 (in this format)

The number format is: 000-000-0000

The range is: B6:B1560


What formula do I use to find out the number of calls each month? and The average number of calls, per telephone number, each month?


Thank you so much for your help.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
DateTimeTown/StatePhone NumberMin/SecCharge
To/FromUsed
22-Aug10:27 AMINCOMING CL203-378-34003.00$0.15
22-Aug10:46 AMNEW HAMPT CO203-378-34001.00$0.05
22-Aug10:55 AMINCOMING CL203-378-34003.00$0.15
22-Aug11:42 AMNEW HAMPT CO203-685-11931.00$0.00
22-Aug3:32 PMINCOMING CL203-378-34002.00$0.10
23-Aug7:23 AMNEW HAMPT CO203-867-50502.00$0.10
23-Aug7:26 AMNEW HAMPT CO203-886-24572.00$0.10

<COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" span=6 width=119><TBODY>
</TBODY>


Here is a sample of what my spreadsheet looks like. The "Date" goes all the way down to "21-May". Cells range from (B6:B1560). I need to do that Average number of calls per month.

Thank you so much.
 
Upvote 0
carla7216 said:
I don't understand the "J2: 1-May-2014". How do I insert that into the formula?
Click to expand...

The instruction means: Enter a first day date of the month/year of interest. The cell J2 is the used in the suggested formula.

=COUNTIFS($B$6:$B$1560,">="&$J2,$B$6:$B$1560,"<="&EOMONTH($J2,0))

which does effect a monthly count of calls. If you want an average, would you please indicate which values must be averaged?
 
Upvote 0
What am I doing wrong?

=COUNTIFS($A$6:$A$1560,">="&8/22/2013,$A$6:$A$1560,"<="&EOMONTH(8/22/2013,0))



I need the Average number of calls within the period of: 8-22-2013 to 5-21-2014. There are several telephone numbers on the spreadsheet.

How do I enter the "date" in the formula?
 
Upvote 0
carla7216 said:
What am I doing wrong?

=COUNTIFS($A$6:$A$1560,">="&8/22/2013,$A$6:$A$1560,"<="&EOMONTH(8/22/2013,0))

I need the Average number of calls within the period of: 8-22-2013 to 5-21-2014. There are several telephone numbers on the spreadsheet.

How do I enter the "date" in the formula?
Click to expand...

Either...

=COUNTIFS($A$6:$A$1560,">=&"8/22/2013",$A$6:$A$1560,"<=&"5/11/2014")

Or...

=COUNTIFS($A$6:$A$1560,">="&"8/22/2013"+0,$A$6:$A$1560,"<="&"5/11/2014"+0)

Or...

=COUNTIFS($A$6:$A$1560,">="&J2,$A$6:$A$1560,"<="&K2)

where J2 = 8/22/2013 and K2 = 5/11/2014

would count all dates between 8/22/2013 and 5/11/2014.

You still have not specified which/what values must be averaged...
 
Upvote 0
What do you mean "what values"?

If I'm understanding you correctly, the values would be the "telephone numbers": 203-378-3400, etc.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Get values from specific date ranges
Replies
5
Views
346
kevin9999
K
S
  • Solved
Counting the number of User Logins per Each Month
Replies
5
Views
131
RobP
R
J
  • Solved
Excel Formula To Average all numbers in one month from list of dates
Replies
2
Views
484
Dave Patton
D
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
H
  • Question
The number of days per month that fall between two dates
Replies
2
Views
449
helpExcelme
H

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

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