telephone list: visualization of calls

[HarryBow]

Hello,

I have an Excel-worksheet with data of people whom I called on their telephone (for a non-profit survey, members/supporters of a charity organization)

Simplified, it looks like this:

Person ID (person that I called)	Call Date	Comment	number of calls
2005002	2014-10-01	10:20 AM: answering machine	4
2005002	2014-10-03	4:30 PM: answering machine	4
2005002	2014-10-06	6:30 PM: busy signal	4
2005002	2014-10-09	6:15 PM: contacted person	4
2005003	2014-10-01	10:30 AM: answering machine	1
2005004	2014-10-01	5:30 PM: busy signal	2
2005004	2014-10-03	7:00 PM: contacted person	2

<tbody>
</tbody>

etcetera

It will contain much more datasets for other persons. The Colum "number of calls" will always only show the final number of calls, not the number of calls at a specific date in time.

What I'm trying to do here: I would like to make a compilation of data / a visualization, that compiles the answers to the following questions:

• How often did I call each person (data is already there, just needs to be visualized in form of a diagram)?
  

• For every person: AM vs PM ratio of calls. The same for the whole set of data.

What am I trying to find out? I want to know (for each person and for the whole dataset) whether we called predominantly in the mornings or in the afternoon, So that I know whether we should try to call again at AM or PM. If I for instance called predominantly in the mornings and haven't reached too much persons yet, I could try to call more in the evenings.

Thanks for now. Oh, and I'm using Excel 2010 Standard.

<tbody>
</tbody>

 

[jim may]

Here's one way:

With your data ADD two formula Columns E & F.. like so

Excel 2012
	A	B	C	D	E	F
1	Person ID (person that I called)	Call Date	Comment	number of calls	Calls Per Person	Call Time
2	2005002	10/1/2014	10:20 AM: answering machine	4	4	AM
3	2005002	10/3/2014	4:30 PM: answering machine	4	0	PM
4	2005002	10/6/2014	6:30 PM: busy signal	4	0	PM
5	2005002	10/9/2014	6:15 PM: contacted person	4	0	PM
6	2005003	10/1/2014	10:30 AM: answering machine	1	1	AM
7	2005004	10/1/2014	5:30 PM: busy signal	2	2	PM
8	2005004	10/3/2014	7:00 PM: contacted person	2	0	PM
Sheet1

Cell Formulas
Range	Formula
E2	=IF(AND(A3<>A2,A2<>A1),1,IF(AND(A3=A2,A2<>A1),D2,))
F2	=IFERROR(IF(FIND("AM",C2)>0,"AM","PM"),"PM")

Then Create Pivot table (Sheet2) as follows...

Excel 2012
	A	B	C	D	E
1
2
3	Sum of Calls Per Person	Column Labels
4	Row Labels	AM	PM	Grand Total
5	2005002	4	0	4
6	2005003	1		1
7	2005004		2	2
8	Grand Total	5	2	7
9
10
11
12
13
14	Pivot Table Fields Used as follows (to get the Above)
15
16		Rows	Person ID
17		Columns	Call Time
18		Values	Sum of - Calls Per Person
Sheet2

 

[mole999]

I loathe cold calls, though you seem to have a tight dedicated list

 

[HarryBow]

Here's one way...

Thanks.

Regarding Sheet 1:

the first formula (that gives me the calls per person): does this formula automatically include all rows that belong to a specific PERSON ID and can I do this somehow automatically for all PERSON IDs I have?

the second formula (that gives me the call time): my initial explaination was actually a bit wrong. The format of the time is actually Continental European style:

	A	B	C	D	E
1	Person ID (person that I called)	Call Date	Comment	number of calls	Call Time
2	2005002	10/1/2014	10:20 answering machine	4	4
3	2005002	10/3/2014	16:30 answering machine	4	0
4	2005002	10/6/2014	18:30 busy signal	4	0
5	2005002	10/9/2014	18:15 contacted person	4	0
6	2005003	10/1/2014	10:30 answering machine	1	1
7	2005004	10/1/2014	17:30 busy signal	2	2
8	2005004	10/3/2014	19:00 contacted person	2	0

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

Sheet1
​

So, I would need a formula that can "see" whether a given time is prior to 12:00 noon time or post noon time. The output can be AM / PM.

Thanks for your help so far. I only worked with formulas in Excel very rarely and in ancient times. I want to get into it again, but I'm not really sure where to start. So, this little non-profit project is a good way for me to practice Excel's advanced functions.

I loathe cold calls, though you seem to have a tight dedicated list

True, but we are not trying to disturb or interrupt anyone. The list is actually not that small, we have quiet a number of members / supporters. The feedback so far was actually good, almost no complaints.

 

[jim may]

Copy Down the two formulas I gave you *** far as you have data in Columns A:D.

Don't have any more time today, must run......

Good luck -- Jim