Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?
share|improve this question

1 Answer 1

up vote 4 down vote accepted

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

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

Your Answer

 
discard

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.