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

Where:
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

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


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"shysong" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> Where:
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > Where:
> > 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
> >
>
>
>