Pivot Table - HELP
Posted by Susie on July 20, 2001 9:13 AM
My database contains info. on employees with primary fields:
Cost center, Employee Name, Gender (Male or Female), Race (Minority or Non-Minority), Age (40+ or <40)
I want my pivot table to count the number of employees in each cost center, the number of females and males, minority and non-minority, Age 40+ and <40. Example:
Cost Center 1000 has total employees 40; 25 female employees and 15 male employees; 20 minority and 20 non-minority; 30 40+ and 10 <40.
How do I organize the database and the pivot table to return the data like this?
I tried putting in my DB an IF formula that if gender is female then 1, if not then, 0. If age >40 then 1, if not 0, etc. I was hoping it would count up the number of 1's in the pivot table, but it returns the total -- it counts the same number for female and male.
Help, please.
Posted by Lars on July 20, 2001 10:16 AM
Add another field for Male and female and monority and non Min then do and find replace for each of those to separate them out then you will be able to count them in the pivot.
Cost Center Name Male Female Min Non Min
You will go from 5 fields to at least 8 as far age wirte an if state to show >40 in one field and <40 in another
after you do this in your database than the pivot can count them.
Posted by Susie on July 20, 2001 10:27 AM
In the Female column, what do I put there? I had previously used my Gender column, and added 2 new columns -- 1 female and 1 male. In the female column I wrote an IF formula that if gender = female then put female, if not then return a blank. Same logic for the male column. Same for age -- I added 2 columns over and under. It still counts the total number in each column even if it's blank.
Can you be more specific on what I should do please?
