Pivot Table Help

[ExcelAmateur2014]

Hi

I am not the best with pivot tables so not sure whether this can be done or not. If not then I will have to find a more complicated way of doings this. If it is possible I would like to know how to do it as I have several variations of what I am asking to achieve.

Basically I have uploaded a sample spreadsheet below via drop box;

https://app.box.com/s/ncrjylmm0k1u0br36odcketu3winqf3w

What I want to do is to be able to pivot off the data on the data tab and try to achieve something similar to what I have showing on the pivot tab, but ideally in a pivot table.

The data tab will have a lot more data in it but the general gist is attached.

I want to pivot per "name" the total amount of times they have answered "Y", "N", "N/A"

I want to then if possible be able to colour the cells red, green and amber based on the mean approach. This is obviously not easy based on the small amount of data in my example but I would like the cells to do the following when there is a wider population.

Green - If within 25% of the mean/average
Amber - If between 25% and 50% of the mean/average
Red - If outside of 50% of the mean/average

Thank you in advance

 

[RoryA]

Create a pivot table from your data, drag Name and Answer from the Field list to the column field area, then drag Name from the field list again but to the Value area this time. Now right-click one of the Answer cells in the pivot table itself, choose Field Settings, and on the Layout and Print tab, check the Show items with no data option.

I'm not sure how feasible those colour bands are without code. You can do percentiles, or within x standard deviations using the built-in options if that's a possibility.