Percentage Possible vs. Percentage of Total

from www.codeforexcelandoutlook.com

Percentage Possible vs. Percentage of Total:

Are you using the correct formula to calculate percentage? Did you know there are two different versions of the "percent of total" you can use to get a slightly different look at your data?

Suppose I have a questionnaire or checklist in Excel with Yes or No answers, and I have named the answer column "AnswerRange".

To count the number of possible answers I could use any of these formulas:

=ROWS(AnswerRange)*COLUMNS(AnswerRange)

(number of Rows in Range * number of Columns in Range = total cells in Range)

=COUNTA(AnswerRange)+COUNTBLANK(AnswerRange)

(Number of filled cells in Range + Number of blanks = total cells in Range)

To count the number of questions actually answered is as simple as this:

=COUNTA(AnswerRange)

But now I want some subtotals. Perhaps I have some conditional formatting or a progress bar to track completion of the form and I need to inform Excel on the amount of questions answered and what percentage are Yes or No answers.

How many questions have been answered overall (either Yes or No)?

=COUNTA(AnswerRange) (same as above)

How many questions have been answered Yes?

Here's where it gets tricky. Do I want the number of Yes answers overall, or the number of Yes answers out of the total that have been answered? An example should illustrate the difference.

Let's say I have ten questions, but only nine have been answered. Out of those nine, five are Yes answers. What is the percentage of Yes answers?

Yes answers overall:

{SUM(IF(AnswerRange="Yes",1,0))/(COUNTA(AnswerRange)+COUNTBLANK(AnswerRange))}

5 / (9 + 1) = 50%

Yes answers out of total answered:

{SUM(IF(AnswerRange="Yes",1,0))/(COUNTA(AnswerRange))}

5 / 9 = 55.56%

Depending on which statistic you want, the answer is different. The difference here is whether blanks are included in the total. If you want to know how many questions were answered Yes, use the first formula. But if you want to know, out of the questions that were answered, how many were answered Yes, use the second formula. Make sense?

A sample workbook demonstrating these formulas is below:

Checklist for Excel 2003/2007/2010

Related Articles:

• August 31, 2011 -- How’d They Do That: Points to Reach Next Level (Actual)
• August 29, 2011 -- How’d They Do That: Points To Reach Next Level (Simple)
• July 27, 2011 -- Add-In Updates for Excel 2010

Percentage Possible vs. Percentage of Total is Copyright © Code For Excel And Outlook - Learn VBA. All Rights Reserved.