Creating Formula for Pivot Tables

[PtrtsGrl]

Hi everyone,

I'm new here and am having some problems. I want to create a pivot table for work that tracks calls, contacts, email, voicemail, and meetings and track ratios for employees. The problem is that all of that information is contained in 1 column, and I can't figure out a formula to track each of those things for all the employees. I thought of creating a giant "if/then" statement, but it just became so complicated.

Can anyone help me / point me in the proper direction.

 

[PtrtsGrl]

I just put the commas in my response since it was a list and all those grammar lessons finally stuck!

 

[PtrtsGrl]

Do you know how I can attach the excel spreadsheet so you'll have a better idea of what I'm trying to do (since I know that I'm not explaining it well).

 

[PtrtsGrl]

Do you know how I can attach the excel spreadsheet so you'll have a better idea of what I'm trying to do (since I know that I'm not explaining it well).

Nevermind, just saw the type on the bottom saying attachments aren't allowed.

 

[etaf]

what does the file CSV they you import look like ?

you could use a share site - like dropbox, skydrive to attach a link to a file
but keep in mind this is a public forum and so the file needs dummy data and nothing that might be data protection act

 

[PtrtsGrl]

Hi etaf,

I have attached a link to the spreadsheet here:

https://skydrive.live.com/redir?res...07&authkey=!ADhJahp_KCw3R3k&ithint=file,.xlsx

Thanks for helping me (I know I'm making this difficult and I apologize -- I get really confused with Excel)

 

[PtrtsGrl]

what does the file CSV they you import look like ?

you could use a share site - like dropbox, skydrive to attach a link to a file
but keep in mind this is a public forum and so the file needs dummy data and nothing that might be data protection act

Hi etaf,

I have attached a link to the spreadsheet here:

https://skydrive.live.com/redir?resi...t=file%2c.xlsx

Thanks for helping me (I know I'm making this difficult and I apologize -- I get really confused with Excel)

 

[etaf]

does this work

shows each call type
members across the top
and % for each activity

if not - then you will need to describe or give in the spreadsheet an example of how you want to see the data and values

Excel Workbook
	A	B	C	D	E	F	G	H
3	Count of Type	Column Labels
4	Row Labels	A	D	J	P	S	Sa	Grand Total
5	Acct Management	0%	0%	0%	6%	0%	94%	100%
6	Appointment	29%	14%	36%	7%	0%	14%	100%
7	Call	26%	10%	41%	10%	0%	14%	100%
8	Canvassing	0%	100%	0%	0%	0%	0%	100%
9	Conference Call	29%	0%	14%	0%	57%	0%	100%
10	Connect/Live Conversation	14%	18%	35%	18%	0%	16%	100%
11	Demo	0%	0%	50%	50%	0%	0%	100%
12	Email	12%	0%	21%	55%	0%	12%	100%
13	Historical Entry	27%	0%	13%	13%	47%	0%	100%
14	Left Voice Mail	26%	11%	11%	35%	0%	18%	100%
15	Meeting	0%	0%	0%	0%	100%	0%	100%
16	Quote	0%	0%	0%	0%	100%	0%	100%
17	Grand Total	20%	7%	26%	29%	2%	15%	100%
Sheet1

 

[PtrtsGrl]

No, I need the actual numbers, then have a column that I can do ratios. That way, I can see how many calls were placed and how many calls were made and how many of those turned into appointments. Hope that makes sense.

Thanks for helping! I really appreciate it.

 

[etaf]

if not - then you will need to describe or give in the spreadsheet an example of how you want to see the data and values

 

[PtrtsGrl]

if not - then you will need to describe or give in the spreadsheet an example of how you want to see the data and values

Looking at the spreadsheet, The numbers up until grand total are for count. I'm trying to get %s for Appointment average and Connect Ratio.

The Appointment average is calculated by: appointments / Grand total

The Connect Ratio is calculated by: Connects / (Connects+Call+Email+Voicemail)

Does that help.