Excel pivot table to show % which are 0

Question

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%

Excellll · Accepted Answer · 2012-03-10 17:29:08Z

up vote 1 down vote accepted

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.

answered Mar 10 '12 at 17:29

Excellll
2,998621

	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 Mar 12 '12 at 20:45
	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 Mar 13 '12 at 4:00
	Got it, thanks! – Chris Mar 13 '12 at 10:35

1 Answer