Apple Intelligence is now available on iPhone, iPad, and Mac!

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Can I create a graph from Yes/No cells?

Hello everyone,


I would like to create a percentage chart from several cells, but those cells don't have numbers, they have either Yes or No. Is there a way to create a graph that represents the percentage of cells that have Yes against the ones that have No?


Thank you so much.


Best,

Rui

iMac, OS X El Capitan (10.11.3), 2.7GHz quad-core Intel Core i5

Posted on Apr 5, 2016 4:54 AM

Reply
13 replies

Apr 5, 2016 11:13 AM in response to anamorphis

I suggest creating a second table where you will summarize the data in your original table... like this:


The table on the top is named "Table 2" and contains columns of data with "YES" and "NO" entries:

User uploaded file


The table on the bottom is named "Summary" and contains columns that summarize the data in "Table 2".


In the table "Summary", then first row is a header row and the first column is a header column which are set by opening the table formatter:

User uploaded file


The formula in cell B2 is:

=COUNTIF(Table 2::A, $A2)


shorthand for this is:

B2=COUNTIF(Table 2::A, $A2)


which means to select cell B2 then type (or copy and paste from here) the formula:

=COUNTIF(Table 2::A, $A2)


select cell B2, copy

select cells B2 and B3, paste. If you have several questions, then select cell B2 thru the row 3 of the last column,

paste


to make the chart, select cells B2 and B3, then choose the pie chart from the charts menu:

User uploaded file

Apr 5, 2016 12:56 PM in response to anamorphis

Hi anamorphis,


I'd suggest a technique similar to Wayne's, but using a stacked bar chart to present the results from many questions in a single chart.

User uploaded file

Formulas:

B26 (first footer row): =COUNTIF(B,"YES")/COUNTA(B)

B27 (2nd footer row): =COUNTIF(B,"NO")/COUNTA(B)

Fill both right to column F. Format cells as Percentage, with one place after the decimal.


Note: Percentages are calculated as the fraction of the total number of cells containing data in the body part of the column. Note the differences in the bars (and percentage totals) for questions 2 and 5. This is due to the difference in recording a non-response to these questions in the rows marked "—>" on the table. With no entry in the Q5 column, the YES percentage here is '# of "YES" divided by 19' In the Q2 column, where the non-responses have been marked "NR" (and are included in the count produced by COUNTA) the YES percentage is '# of "YES" divided by 24'.


If the NRs are recorded, the table calculates, and the chart displays, the percentages with repect to the total number of surveys returned.

If the NRs are left blank, the table calculates, and the chart displays, the percentages with repect to the total number of answers to each question.


Regards,

Barry

Apr 6, 2016 6:55 AM in response to Barry

Hi guys,


Thank you so much for taking the time. Unfortunately, after giving it some time and trying both suggestions, I haven't been able to achieve what I need, maybe because the table I have is slightly different from the ones you used as an example. Or maybe it's just because I really didn't understood what you are telling me. Anyway, bellow is a screenshot of the table where my data is. Maybe it makes more sense to you this way.

User uploaded file

So, what I need is to calculate the percentage of yes vs no on row B. How will I do this?


Again, thank you so much. I really appreciate it.


Best,

Rui

Apr 6, 2016 10:08 AM in response to Wayne Contello

Hello again Wayne,


I think we're getting closer, but it appears something is not quite right yet. Have a look a the following screenshot. It's the same table, but with different numbers of "yes" and "no" in each one. It looks like there's something wrong with the calculation. Do you think you can spot it? Table 3 shouldn't be 600%, right? And I guess that table 4 should be 100%, I suppose.

User uploaded file


Thank you!

Apr 6, 2016 11:30 AM in response to anamorphis

"Table 3 shouldn't be 600%, right? And I guess that table 4 should be 100%, I suppose."


Table 3 correctly reports "the percentage of yes vs no on row column B," which is what you asked for. There are six times as many yesses as there ar nos in table 3. And for the same question, the 'division by zero' error triangle is also correct for table 4.


I think what you actually wanted is "the percentage of answers that are yes (and the percentage of answers that are no).


Yes%: =COUNTIF(B2:B8,"yes"/COUNTA(B2:B8)

No%: =COUNTIF(B2:B8,"no"/COUNTA(B2:B8)


COUNTIF counts only the cells containing the specified value (eg. "yes")

COUNTA counts all of the cells containing any text (or other value type).


Results for these formulas, to the nearest tenth of a percent will be:


1: 14.3%

2: 0.0%

3: 85.7%

4: 100.0 %


These are the same formulas as presented in my earlier post that included the bar graph.


Regards,

Barry

Apr 7, 2016 12:38 AM in response to Barry

Hi Barry,


That is exactly correct. The thing is, and I believe this is what's causing problems, when I use the "/" symbol, my version of Numbers, because I'm in Portugal, converts it to "÷". That's not the symbol you want, right?

Let me try and show you how the formula looks like in my app:


CONTAR.SE(B2:B8;"yes")÷CONTAR(B2:B8)


CONTAR.SE means COUNTIF, and CONTAR is the local version of COUNTA. And yes, we use ";" instead of ",". I learned this the hard way from previous formulas.


Could that be the one thing that is messing up the formula?


Thank you!

Apr 7, 2016 12:56 AM in response to anamorphis

Hi Rui,


Substitution of / by ÷ (and * by x) is (I think) universal for Numbers 3. Type /, and Numbers 3 will display ÷, but the formula will work correctly.


Use of the , or ; as the list separator depends on your region settings. In regions where the decimal separator is . the comma is both the thousands separator (in numbers) and the list separator (in formulas). Where the decimal separator is a , and the thousands separator is a . the list separator is a ;


North Americans (including English speaking Canadians like me) tend to miss that as they're so used to using the comma in formulas.


There are four COUNT functions:


COUNT

COUNTA

COUNTIF

COUNTIFS


I suspect CONTAR is the equivalent of COUNT, rather than COUNTA.

CONTAR.VAL might be the equivalent of COUNTA.


COUNT counts only numerical (and number-like) values.

COUNTA counts any cell containing data of any type.


Regards,

Barry

Apr 7, 2016 10:37 AM in response to anamorphis

Hi Rui,

Here are a couple of references you might find useful. The first is similar to what's already available in Numbers, but spreads the functions out on a single (large) 'page.'

250 + Functions in Numbers


The second is a list in English and Portuguese of Excel functions. Not all are supported in Numbers, but the list is useful for translations, and does describe each function (in English). For most, the descriptions also fit what the function does in Numbers.


Regards,

Barry

Can I create a graph from Yes/No cells?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.