Excel – How to plot survey results on Excel

chartsmicrosoft 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  Yes 5   No answer
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?

Best Answer

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:

pie chart

Related Question