The ROW() bit gets it to return both choice 1 and then choice 2, which
are the count of Yes and No. I believe it's actually an array formula.

Cheers
Andy

Dallman Ross wrote:
In , Andy Pope
spake thusly:


Works for me in both xl2k and xl2003.

Here is a file if that helps.
http://www.andypope.info/ngs/ng54.htm


Really nice, Andy. I'm sure the OP will be pleased. I know I am!

Btw, maybe you can exaplain the ROW($1:$2) part of the formula.
That loses me a bit. Also, I had no idea one could use a
'{Yes","No"}' like that as an X-axis. Cool beans!
I'm glad I suggested named ranges, even if I couldn't make
it work on my own. I'll keep your file around.

-dman-

================================================== ====================

Dallman Ross wrote:

In , Andy Pope
spake thusly:



Try this. Assumes yes/no's are in A2:A21

Create a named range

ChtData:
=CHOOSE(ROW(Sheet1!$1:$2),COUNTIF(Sheet1!$A$2: $A$21,"Yes"),COUNTIF(Sheet1!$A$2:$A$21,"No"))

The in a column chart set the series formula to

=SERIES(,{"Yes","No"},Book1!ChtData,1)


Andy, looks very interesting, indeed. I can't get it to work in Excel
2002, however. Won't take to a chart.

-dman-

=====================================


Dallman Ross wrote:


In , 00KobeBrian
spake thusly:




Thanks. How do you refer to the defined name in a spreadsheet?


Reviewing my testing, I'm afraid I misled you. My first statement
about it seems to be more correct. I can't get a chart of counts
to work from a named "range." Maybe there's a way -- probably an
array formula -- but I don't see it right now. Possibly one
could construct an array constant (see that in Help) and give
that a name. I'd like to see an example of it, if someone is
able to mock one up that charts nicely.

If there is a way, it would be accessed in the chart like so:

=Sheet1!myName

============================================= ==========



"Dallman Ross" <dman@localhost. wrote in message
...




In , Dallman Ross
<dman@localhost. spake thusly:




In , 00KobeBrian
spake thusly:




Say I have a column of data and it contains either yes
or no and I want to draw a chart with how many yes and
how many no. And I don't want to count it one the
spreadsheet. Instead I want to get it from a chart. How can
I do it? Thanks.

I suspect you'll need to use the sheet, though you could do
it in hidden cells, columns, rows, or sheets, or in other
workbooks.

It turns out I was wrong. You can use a named range. E.g., go
to the Menu bar: Insert - Name - Define. Give your range a
name. Give it a formula, such as:

=COUNTIF(Sheet1!G:G,"Yes")

We could name that one "Yes". Do a similar thing with one
called "No".

No go to your chart. The value for the series would be,
e.g., "=Book1!Yes".

-dman-