Pivot table question
 

Hello,

I have another problem, this time using Pivot tables.

Using Win XP SP2, on Excel 2000 (SP3).

I have a list with the following data sets: employee position,
department and name of training session attended. This is a list of
employees that have followed a training sessions. (1 empl. can follow
more than 1 session)

The desired result is a table displaying the total number of employes
who have followed a training session, with the information sorted by
position and department. (there can be the same position in different
dpts.)

I was able to construct the table but my problem is that I get a total
of all training sessions followed - not the number of all employes who
have followed a session. My number is much bigger than it should be.

I cannot modifiy the field options to get the numbers of different
employes who have followed a session.

See attached pics. Data is an example of the data source, and pivot
table is my table with the total counts. Note that employee's position
is on the left (catégorie poste) and department is on the top (service).
Instead of 868, I should have around 300... (since the table counts
total training sessions instead of total number of employees trained)

Can anyone help me???

thank you very much!!!

Louis


+-------------------------------------------------------------------+
|Filename: DATA.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4349 |
+-------------------------------------------------------------------+

--
louisp
------------------------------------------------------------------------
louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880
View this thread: http://www.excelforum.com/showthread...hreadid=511160

Pivot table question
 

Hi Louis

Since in your example data, the same person belongs to more than one
department, it is not surprising to me that the total number counted in
the PT exceeds the number of employees.
If you pulled Emp Code or Last Name to the Row area of the PT, instead
of Department, then your count would be of employees against training
sessions, and, the total would match your number of employees.

You could take Department to a Page field, and then see the total number
of employees from a Department who had attended which course, but the
total of each of these Department totals would come back to the 868
value you currently have.

I hope this helps a little.

--
Regards

Roger Govier


"louisp" wrote in
message ...

Hello,

I have another problem, this time using Pivot tables.

Using Win XP SP2, on Excel 2000 (SP3).

I have a list with the following data sets: employee position,
department and name of training session attended. This is a list of
employees that have followed a training sessions. (1 empl. can follow
more than 1 session)

The desired result is a table displaying the total number of employes
who have followed a training session, with the information sorted by
position and department. (there can be the same position in different
dpts.)

I was able to construct the table but my problem is that I get a total
of all training sessions followed - not the number of all employes who
have followed a session. My number is much bigger than it should be.

I cannot modifiy the field options to get the numbers of different
employes who have followed a session.

See attached pics. Data is an example of the data source, and pivot
table is my table with the total counts. Note that employee's
position
is on the left (catégorie poste) and department is on the top
(service).
Instead of 868, I should have around 300... (since the table counts
total training sessions instead of total number of employees trained)

Can anyone help me???

thank you very much!!!

Louis


+-------------------------------------------------------------------+
|Filename: DATA.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4349 |
+-------------------------------------------------------------------+

--
louisp
------------------------------------------------------------------------
louisp's Profile:
http://www.excelforum.com/member.php...o&userid=25880
View this thread:
http://www.excelforum.com/showthread...hreadid=511160

Pivot table question
 

hello roger,

thank you for you help. but I still get the same end result.

FYI, an employee can only be in 1 department and position. (one of the
examples I gave was "englishized" for comprehension purposes..)

What does increase the number, is the fact that any given employee can
follow more than 1 training session. Instead of 868, I should have
around 300, which is the total number of participants which have
followed a training session.

so if an employee followed 4 sessions, he should be counted as 1 and
not 4. That's the crux, I cannot change the count... I was looking into
doing a count.if but cannot do so since this is a pivot table, and I
could not specify conditions... (could this work????)

I'm now thinking of rearranging the data/table.... Theres also the
possibility of creating a calculated field... but this also seems a
little complicated...


hoping someone might help me figure this out... thx
louis


--
louisp
------------------------------------------------------------------------
louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880
View this thread: http://www.excelforum.com/showthread...hreadid=511160

Pivot table question
 

Hi Louis

Then go to Table Options and remove the Grand Totals by row and Grand
Totals by column check marks.
The data is the data and will represent the total number of courses
attended. All you can do is tell the PT you don't want totals for the
displayed items.
If you need to check the total number of employees, then still pull Name
into the row area alongside Department.
Click on Department and choose No Subtotals.
Employee name will be in column B.
Insert a new Row 1, above the Pivot table, and in B1 enter
=COUNTA(B6:B500)

--
Regards

Roger Govier


"louisp" wrote in
message ...

hello roger,

thank you for you help. but I still get the same end result.

FYI, an employee can only be in 1 department and position. (one of the
examples I gave was "englishized" for comprehension purposes..)

What does increase the number, is the fact that any given employee can
follow more than 1 training session. Instead of 868, I should have
around 300, which is the total number of participants which have
followed a training session.

so if an employee followed 4 sessions, he should be counted as 1 and
not 4. That's the crux, I cannot change the count... I was looking
into
doing a count.if but cannot do so since this is a pivot table, and I
could not specify conditions... (could this work????)

I'm now thinking of rearranging the data/table.... Theres also the
possibility of creating a calculated field... but this also seems a
little complicated...


hoping someone might help me figure this out... thx
louis


--
louisp
------------------------------------------------------------------------
louisp's Profile:
http://www.excelforum.com/member.php...o&userid=25880
View this thread:
http://www.excelforum.com/showthread...hreadid=511160

Pivot table question
 

hello again roger,

Your solution does get me the desired result but it is not dynamic;
everytime I change departments, i need to redo a count for all
positions. (since what you suggested was not part of the PT and the
number of rows displayed changes).

I thought there was a way to get just the number of employees who
followed a training session - easily. I guess not...

I will continue looking for a solution when I can... in the meanwhile I
suppose this will have to do.

I get the impression the data would have to be reformated a little to
prevent the sum of all sessions followed...

thank you for your help

louis


--
louisp
------------------------------------------------------------------------
louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880
View this thread: http://www.excelforum.com/showthread...hreadid=511160