Excel pivot table to show % which are 0

0

Here's some pretend data:

    Apple       4
    Apple       0
    Apple       1
    Banana      4
    Carrot      0
    Apple       0
    Apple       2

I want to show the percent of each food that is exactly 0. So the ideal output:

   Apple        20%
   Banana       0%
   Carrot       100%

Chris

Posted 2012-03-10T10:06:17.150

Reputation: 538

Answers

1

Add a column to your data with the following formula filled down:

=--(B1=0)

Then set up your pivot table to calculate the average of the new column.

Excellll

Posted 2012-03-10T10:06:17.150

Reputation: 11 857

I'm not sure I understand what that's doing, and it doesn't seem to work for me. Could you confirm what I should see there? – Chris – 2012-03-12T20:45:30.373

The formula returns '1' for records with a zero value in column B, and '0' for those that do not. The pivot table (with rows grouped by Column A) should be set up with the average of this new column as the value. For example, the pivot table will display the average of {0,1,0,1,0} for 'Apple' -- 0.40. You can change the formatting of the pivot table to display the averages as percentages. – Excellll – 2012-03-13T04:00:42.303

Asked: 2012-03-10T10:06:17.150

Viewed: 160 times

Active: 2012-03-10T17:29:08.850