# How to plot survey results on Excel?

I would like to plot the average of these data samples:

``````1. Contribution to the project
2. Affiliation with project owners
3. Level of expertise
4. Learning Curve

Yes No  3   2
No  No  3   2
No  No  4   3
No  No  5   3
No  No  3   3
Yes Yes 5   4
No  No  4   3
No  No  3   2
No  Yes 2   4
No  No  5   2
No  No  2   2
No  No  3   3
...
``````

I know how to do the average of each numeric column, but I have a few questions for you:

• If the column contains non numeric values (e.g. "no answer") the average doesn't work. Is there a workaround for this?
• How can I count the yes/no results and plot only a percentage?
-

If the column contains non numeric values (e.g. "no answer") the average doesn't work. Is there a workaround for this?

I assume you're doing an average like this: `=AVERAGE(C1:C13)`. If so, the formula will simply skip that cells that have text in them. So, in your example, you have 13 rows, the average formula will be dividing by 12, rather than 13. You can check this by replacing the "No answer" with a 0:

``````AVERAGE(C1:13) = 2.75 (has "No Answer")
AVERAGE(C1:13) = 2.54 (has a 0)
``````

So it's up to you whether you want to count the people who didn't answer as zeros, or just not include them in your statistics.

How can I count the yes/no results and plot only a percentage?

This one is easy - you'll use Excel's `COUNTIF` formula.

In a new set of cells, you'll want to count each yes or no. For your example, I put my yes in K2 and No in K3. You'll want to use the `COUNTIF` like so:

``````For the Yes row (K2): =COUNTIF(A:A, "Yes")
For the No row  (K3): =COUNTIF(A:A, "No")
``````

Then just use a pie chart referencing the countif cells, and it should look like this:

-
+1 Nice. I was figuring how to go about the Yes/No condition. Although, in OpenOffice. – Sathya Jun 1 '10 at 17:33
Great answer. I would be tempted to adapt the countif to count "yes" and "not yes" to make sure to catch any oddities that might occur in the data now or in the future (such as "no answer"). So for example using =COUNTIF(A:A, "<>Yes") should work. Only problem then of course is if someone enters "Y" instead of "Yes" it still does not count as a yes (no worse than the current method, but possibly more misleading). – AdamV Jun 1 '10 at 20:52