Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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%
share|improve this question
up vote 1 down vote accepted

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


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

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.