Help with PivotTable and Percentages?

[jbradley]

Hey guys,

I may have gotten in over my head on this one. I was helping out a little at a local non-profit animal shelter. Clients were given a survey to fill out and the data was entered into a spreadsheet. That spreadsheet was given to me in hopes to create some sort of way to analyze and look at it quickly, efficiently and based on what each manager or department wanted. The heading on the raw data MAY be the biggest road block but i'm having trouble figuring out the best way to go about it. Any help would be appreciated. Current headings are: Date, Zip code, Animal breed (Dog, cat, other), Animal age (Young, adult, senior), Animal gender(Male, Female), Reason for coming (Wellness, Vaccine, Spray / Neuter, Grooming),
How did you hear about us (4 options here too), and What did you like most (5 possible responses).

So, when I create the pivot table I've used the Reason for coming as the main row label, since that is currently what they consider the most important information. Which leaves me a Row on the left with not only the 4 options, but 7 rows because some people came for multiple reasons. So the rows it gives me are some hybrid, Wellness / Vaccine (I know that's because how it was entered on the raw data) ... How could I fix that, the people who selected 2-3-4 options should be included into an overall count for each of the 4 answers if I were to sort the data by say Zip code. For now, I used the Animal type to fill out the colums, and I have the animal age, gender, zipcode, reason for coming and what they liked most all as sortable options in the filter.

At that point, even if I left the hybrid answers alone, I'm having trouble spitting out a % for the shelter manager. As in, if they chose zip code 1 the pivot table will would only show animals in from that zip on every day ... How/Where (this is where my inexperience really kicks in) would I put some sort of function to show the % of wellness animals vs Vaccine vs Spray / Neuter vs Grooming? I thought about just putting it somewhere belowe the actual pivot table, I just wasn't sure if that's common/proper or is there another way? The other issue is if the data was sorted by zip code, gender and their reason for being there it might cause the table to expand over the manual entered cells?

Any help would be GREATLY appreciated, I've worked with Excel on a basic level and a little intermediate which is why I volenteered to help. Seems I bit off a little more than I could chew, but I learn fast and understand most concepts so with a little help hopefully I'll be able to get this correct. I have access to the raw data and would be able to easily create 4 new columns labeled Welness, Vaccine, Spray / Neuter, Grooming and put yes/no if that would be easier? I'm just at a little mental road block and not able to get around it =(

[jbradley]

Book1.xlsxI've created a small workbook to simulate most (not the reason / what liked most cause that data is a little low on list at moment). Maybe it'll help.