Can I create a graph from Yes/No cells?

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:

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:

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:

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.

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

in the cell where you want the yes-to-no-ratio type (or copy and paste from here) the formula:

=countif(B2:B8. "yes")/countif(B2:B8. "no")

in words, this counts the number of yes entries, then divides by the count of no entries

"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

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

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

Just in case you missed it you can get Numbers-specific formula help in Portuguese (and many other languages) right from 'Help > Formulas & Functions Help' in Numbers. Lots of helpful examples there.

Click next to the little globe as shown, and choose from a list that looks like this:

SG