Calculating the average of a sum in Pivot Table
 

Hi all, hope someone can help me. I have data from a survey where we asked a
bunch of people to look at a list of tasks and tell us whether or not they
performed each task on the list. The dataset looks like this:

ID JT TN PF
1 1 1 0
1 1 2 1
1 1 3 1
1 1 4 0
2 1 1 0
2 1 2 1
2 1 3 1
2 1 4 0

Whe
ID = The person's ID number
JT = The person's job title
TN = The number of the task in question
PF = Whether the person performs the task (0=no, 1=yes)

What I want to know is on the average how many tasks were endorsed by people
of each job title. I created a Pivot table, which gives me the count of
tasks performed by each person, organized by job title (since they are 0-1,
all I have to do is look at the number of 1's to know how many tasks each
person performed). However, the total line for each job title value gives me
the Sum of that count, rather than the average. How can I change that
summary line for job title to Average from Sum?

Thanks in advance,

Sylvia

PS, for those of you who are more visual, this is what I mean.

My pivot table currently looks like this:
JT ID Count of Perform
1 1 165
1 2 130
1 3 100
Total 395
2 1 165
2 2 130
3 3 100
Total 395

I want:
JT ID Count of Perform
1 1 165
1 2 130
1 3 100
Average 131.66
2 1 165
2 2 130
2 3 100
Total 131.66

Calculating the average of a sum in Pivot Table
 

Right click one of the totals and select field settings, there you can
change to average


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"shysong" wrote in message
...
Hi all, hope someone can help me. I have data from a survey where we
asked a
bunch of people to look at a list of tasks and tell us whether or not they
performed each task on the list. The dataset looks like this:

ID JT TN PF
1 1 1 0
1 1 2 1
1 1 3 1
1 1 4 0
2 1 1 0
2 1 2 1
2 1 3 1
2 1 4 0

Whe
ID = The person's ID number
JT = The person's job title
TN = The number of the task in question
PF = Whether the person performs the task (0=no, 1=yes)

What I want to know is on the average how many tasks were endorsed by
people
of each job title. I created a Pivot table, which gives me the count of
tasks performed by each person, organized by job title (since they are
0-1,
all I have to do is look at the number of 1's to know how many tasks each
person performed). However, the total line for each job title value gives
me
the Sum of that count, rather than the average. How can I change that
summary line for job title to Average from Sum?

Thanks in advance,

Sylvia

PS, for those of you who are more visual, this is what I mean.

My pivot table currently looks like this:
JT ID Count of Perform
1 1 165
1 2 130
1 3 100
Total 395
2 1 165
2 2 130
3 3 100
Total 395

I want:
JT ID Count of Perform
1 1 165
1 2 130
1 3 100
Average 131.66
2 1 165
2 2 130
2 3 100
Total 131.66

Calculating the average of a sum in Pivot Table
 

Tried that already. That only changes everything to zero's and 1's (remember
I asked it to count all the one's for me, which is what gives the number of
tasks performed. The average of a bunch of 1's is 1. Maybe I need to set up
the pivot table differently?

"Peo Sjoblom" wrote:

Right click one of the totals and select field settings, there you can
change to average


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"shysong" wrote in message
...
Hi all, hope someone can help me. I have data from a survey where we
asked a
bunch of people to look at a list of tasks and tell us whether or not they
performed each task on the list. The dataset looks like this:

ID JT TN PF
1 1 1 0
1 1 2 1
1 1 3 1
1 1 4 0
2 1 1 0
2 1 2 1
2 1 3 1
2 1 4 0

Whe
ID = The person's ID number
JT = The person's job title
TN = The number of the task in question
PF = Whether the person performs the task (0=no, 1=yes)

What I want to know is on the average how many tasks were endorsed by
people
of each job title. I created a Pivot table, which gives me the count of
tasks performed by each person, organized by job title (since they are
0-1,
all I have to do is look at the number of 1's to know how many tasks each
person performed). However, the total line for each job title value gives
me
the Sum of that count, rather than the average. How can I change that
summary line for job title to Average from Sum?

Thanks in advance,

Sylvia

PS, for those of you who are more visual, this is what I mean.

My pivot table currently looks like this:
JT ID Count of Perform
1 1 165
1 2 130
1 3 100
Total 395
2 1 165
2 2 130
3 3 100
Total 395

I want:
JT ID Count of Perform
1 1 165
1 2 130
1 3 100
Average 131.66
2 1 165
2 2 130
2 3 100
Total 131.66