chart question #4

Can anyone please give me some guideline on how to draw a chart with y-axis 
is the count of the item in x-axis?  I am using Excel 97. Thanks. 


0
00KobeBrian
11/1/2006 5:03:07 AM
excel 39879 articles. 2 followers. Follow

15 Replies
473 Views

Similar Articles

[PageSpeed] 56

I am not certain I fully understand your question, but I am going to give it 
a stab. Excel charts are very flexible, and I think you need to keep in mind 
that you need to have the right data in place to draw the chart. I.e. don't 
focus on the chart itself, focus on the data in the spreadsheet you are 
trying to chart, and once that is in place, your chart will look the way you 
want.
-- 
ChrisJ
Omaha


"00KobeBrian" wrote:

> Can anyone please give me some guideline on how to draw a chart with y-axis 
> is the count of the item in x-axis?  I am using Excel 97. Thanks. 
> 
> 
> 
0
ChrisJ (13)
11/1/2006 12:31:01 PM
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.


"ChrisJ" <ChrisJ@discussions.microsoft.com> wrote in message 
news:0EBA05B5-31AC-4559-8235-73B2101F1953@microsoft.com...
>I am not certain I fully understand your question, but I am going to give 
>it
> a stab. Excel charts are very flexible, and I think you need to keep in 
> mind
> that you need to have the right data in place to draw the chart. I.e. 
> don't
> focus on the chart itself, focus on the data in the spreadsheet you are
> trying to chart, and once that is in place, your chart will look the way 
> you
> want.
> -- 
> ChrisJ
> Omaha
>
>
> "00KobeBrian" wrote:
>
>> Can anyone please give me some guideline on how to draw a chart with 
>> y-axis
>> is the count of the item in x-axis?  I am using Excel 97. Thanks.
>>
>>
>> 


0
00KobeBrian
11/2/2006 1:27:53 AM
In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian <a@b.com>
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 might be possible to generate the charts with VBA code.
But it seems to me that the code would be quite a mess in comparison
to simply using the spreadsheet.

-dman-

===============================
> "ChrisJ" <ChrisJ@discussions.microsoft.com> wrote in message
> news:0EBA05B5-31AC-4559-8235-73B2101F1953@microsoft.com...
>
> >I am not certain I fully understand your question, but I am
> >going to give it a stab. Excel charts are very flexible, and I
> >think you need to keep in mind that you need to have the right
> >data in place to draw the chart. I.e. don't focus on the chart
> >itself, focus on the data in the spreadsheet you are trying to
> >chart, and once that is in place, your chart will look the way
> >you want. -- ChrisJ Omaha
> >
> > "00KobeBrian" wrote:
> >
> >> Can anyone please give me some guideline on how to draw a
> >> chart with y-axis is the count of the item in x-axis?  I am
> >> using Excel 97. Thanks.
0
Dallman
11/2/2006 12:19:42 PM
In <eicnou$om3$1@reader2.panix.com>, Dallman Ross <dman@localhost.>
spake thusly:

> In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian <a@b.com>
> 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-
0
Dallman
11/2/2006 12:51:09 PM
Thanks. How do you refer to the defined name in a spreadsheet?


"Dallman Ross" <dman@localhost.> wrote in message 
news:eicpjt$om3$3@reader2.panix.com...
> In <eicnou$om3$1@reader2.panix.com>, Dallman Ross <dman@localhost.>
> spake thusly:
>
>> In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian <a@b.com>
>> 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- 


0
00KobeBrian
11/3/2006 2:56:49 AM
In <OD9v3Ov$GHA.204@TK2MSFTNGP04.phx.gbl>, 00KobeBrian <a@b.com>
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
> news:eicpjt$om3$3@reader2.panix.com...
>
> > In <eicnou$om3$1@reader2.panix.com>, Dallman Ross
> > <dman@localhost.> spake thusly:
> >
> >> In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian
> >> <a@b.com> 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-
0
Dallman
11/3/2006 9:08:40 AM
Hi,

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)

Cheers
Andy

Dallman Ross wrote:
> In <OD9v3Ov$GHA.204@TK2MSFTNGP04.phx.gbl>, 00KobeBrian <a@b.com>
> 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
>>news:eicpjt$om3$3@reader2.panix.com...
>>
>>
>>>In <eicnou$om3$1@reader2.panix.com>, Dallman Ross
>>><dman@localhost.> spake thusly:
>>>
>>>
>>>>In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian
>>>><a@b.com> 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-
0
andy9699 (3616)
11/3/2006 9:59:45 AM
In <OndYR7y$GHA.1196@TK2MSFTNGP02.phx.gbl>, Andy Pope
<andy@andypope.info> 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 <OD9v3Ov$GHA.204@TK2MSFTNGP04.phx.gbl>, 00KobeBrian <a@b.com>
> > 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
> >>news:eicpjt$om3$3@reader2.panix.com...
> >>
> >>
> >>>In <eicnou$om3$1@reader2.panix.com>, Dallman Ross
> >>><dman@localhost.> spake thusly:
> >>>
> >>>
> >>>>In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian
> >>>><a@b.com> 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-
0
Dallman
11/3/2006 1:10:32 PM
Works for me in both xl2k and xl2003.

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

Cheers
Andy

Dallman Ross wrote:
> In <OndYR7y$GHA.1196@TK2MSFTNGP02.phx.gbl>, Andy Pope
> <andy@andypope.info> 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 <OD9v3Ov$GHA.204@TK2MSFTNGP04.phx.gbl>, 00KobeBrian <a@b.com>
>>>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
>>>>news:eicpjt$om3$3@reader2.panix.com...
>>>>
>>>>
>>>>
>>>>>In <eicnou$om3$1@reader2.panix.com>, Dallman Ross
>>>>><dman@localhost.> spake thusly:
>>>>>
>>>>>
>>>>>
>>>>>>In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian
>>>>>><a@b.com> 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-
0
andy9699 (3616)
11/3/2006 1:36:24 PM
In <O4GHV00$GHA.4212@TK2MSFTNGP02.phx.gbl>, Andy Pope
<andy@andypope.info> 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 <OndYR7y$GHA.1196@TK2MSFTNGP02.phx.gbl>, Andy Pope
> > <andy@andypope.info> 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 <OD9v3Ov$GHA.204@TK2MSFTNGP04.phx.gbl>, 00KobeBrian <a@b.com>
> >>>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
> >>>>news:eicpjt$om3$3@reader2.panix.com...
> >>>>
> >>>>
> >>>>
> >>>>>In <eicnou$om3$1@reader2.panix.com>, Dallman Ross
> >>>>><dman@localhost.> spake thusly:
> >>>>>
> >>>>>
> >>>>>
> >>>>>>In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian
> >>>>>><a@b.com> 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-
0
Dallman
11/3/2006 3:35:33 PM
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 <O4GHV00$GHA.4212@TK2MSFTNGP02.phx.gbl>, Andy Pope
> <andy@andypope.info> 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 <OndYR7y$GHA.1196@TK2MSFTNGP02.phx.gbl>, Andy Pope
>>><andy@andypope.info> 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 <OD9v3Ov$GHA.204@TK2MSFTNGP04.phx.gbl>, 00KobeBrian <a@b.com>
>>>>>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
>>>>>>news:eicpjt$om3$3@reader2.panix.com...
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>In <eicnou$om3$1@reader2.panix.com>, Dallman Ross
>>>>>>><dman@localhost.> spake thusly:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian
>>>>>>>><a@b.com> 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-
0
andy9699 (3616)
11/3/2006 4:15:41 PM
In <uj1mVN2$GHA.2128@TK2MSFTNGP03.phx.gbl>, Andy Pope
<andy@andypope.info> spake thusly:

> 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.

Gotcha.  Thanks again.

-dman-

==============================================> 
> Dallman Ross wrote:
> > In <O4GHV00$GHA.4212@TK2MSFTNGP02.phx.gbl>, Andy Pope
> > <andy@andypope.info> 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 <OndYR7y$GHA.1196@TK2MSFTNGP02.phx.gbl>, Andy Pope
> >>><andy@andypope.info> 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 <OD9v3Ov$GHA.204@TK2MSFTNGP04.phx.gbl>, 00KobeBrian <a@b.com>
> >>>>>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
> >>>>>>news:eicpjt$om3$3@reader2.panix.com...
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>In <eicnou$om3$1@reader2.panix.com>, Dallman Ross
> >>>>>>><dman@localhost.> spake thusly:
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>In <OPIhg4h$GHA.3836@TK2MSFTNGP02.phx.gbl>, 00KobeBrian
> >>>>>>>><a@b.com> 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-
0
Dallman
11/3/2006 4:26:25 PM
Okay, Andy, here's a new challenge: can we add an average line
to a chart without bothering with a dummy column or range filled with
the average?

I just tried a named range and used this for grins, but also
because I don't really know what I'm doing, here, and just wanted
to try something:

  =CHOOSE(row($a$1:$a$20),AVERAGE(Sheet1!$B$2:$B$21))

It does plot something on the graph, but not something I'd
call an average of the data I had.

-dman-
0
Dallman
11/3/2006 5:20:25 PM
Here you go. assumes series values are in C3:C6, which is 4 values.
The row numbers raised to the power 0 is 1. So you get the average 4 times.

=AVERAGE(Sheet1!$C$3:$C$6)*(ROW(Sheet1!$1:$4)^0)

Cheers
Andy

Dallman Ross wrote:
> Okay, Andy, here's a new challenge: can we add an average line
> to a chart without bothering with a dummy column or range filled with
> the average?
> 
> I just tried a named range and used this for grins, but also
> because I don't really know what I'm doing, here, and just wanted
> to try something:
> 
>   =CHOOSE(row($a$1:$a$20),AVERAGE(Sheet1!$B$2:$B$21))
> 
> It does plot something on the graph, but not something I'd
> call an average of the data I had.
> 
> -dman-
0
andy9699 (3616)
11/3/2006 6:17:57 PM
In <#LdMqR3$GHA.2128@TK2MSFTNGP03.phx.gbl>, Andy Pope
<andy@andypope.info> spake thusly:

> Here you go. assumes series values are in C3:C6, which is 4
> values.  The row numbers raised to the power 0 is 1. So you get
> the average 4 times.
>
> =AVERAGE(Sheet1!$C$3:$C$6)*(ROW(Sheet1!$1:$4)^0)

Impressive!  Thanks, Andy.

-dman-

===================================================
> Dallman Ross wrote:
>
> > Okay, Andy, here's a new challenge: can we add an average line
> > to a chart without bothering with a dummy column or range
> > filled with the average?

0
Dallman
11/3/2006 8:29:32 PM
Reply:

Similar Artilces:

Outlook across firewall #4
We are a small franchise and access our email using Outlook 2000. The head office has the Exchange server that hosts our email across a VPN using a Raptor firewall. 2 out of 6 or our Windows 2000 SP3 clients on the same network are having problems when they try to access their email for a second time. The error they get is "Unable to access Exchange Server". The only way to access the Exchange server again is to restart Windows2000. Then it will fail with the same error if they click on the Send/Receive a second time. Thanks in advance. ...

CRM 4.0 queue issue
Hi, I've run into this in CRM 3.0 (and honestly, I don't know what I did to fix it). But now it's happening in CRM 4.0. I have a queue, and a user with an email address. I want all email that is sent to this user to also appear in the CRM queue. I have it working, it's just that I'm getting everything twice. Once as an email from the original sender, and once from the recipient to the recipient, with the original email as an attachment. For example, kbriscoe@domain.com sends an email to ITSupport@domain.com (Our queue's email address is ITSupport@domain.com). T...

control charts #2
Might some direct me to some good examples of control charts and or directions for creating. I have been asked to get up to speed on them and this is somehting new for me. Thanks Jon Peltier has information on creating them in EXCEL, http://www.peltiertech.com/Excel/Charts/statscharts.html I'd suggest you do some reading on control charts from other sites. There should be something here that could help you. http://www.isixsigma.com/st/control_charts/ "Todd F." wrote: > Might some direct me to some good examples of control charts and or > directions for creatin...

Dynamic series in Chart
Guys, I have a spreadsheet with timestamps on col A, then Col B contains the values correspond to timestamps in ColA (Col C thru Col M are similar to Col B as well). I am trying to build a chart in function of time (meaning Values for the X axis would be B2..M2, and Y axis would be B3..M3 for the 1 series, then B3..M3 for the values in x-axis, and B4..M4 for Y axis, so on...). Number of rows is subject to change (time stamp entries), and I am only interested for the last 21 entries (if there is less than 21 entries, plot them all, otherwise, only plot the last 21 entries). For ...

CRM 4.0: WebSite deployment problem
CRM 4.0: I cannot deploy my WebSite in the CRM WebSite, below is the error: 'Microsoft.Crm.WebServices.Crm2007.MultipleOrganizationSoapHeaderAuthentica­tionProvider, Microsoft.Crm.WebServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' doesn't exist. Parameter name: Microsoft.Crm.WebServices.Crm2007.MultipleOrganizationSoapHeaderAuthenticat­ionProvider, Microsoft.Crm.WebServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 Description: An unhandled exception occurred during the execution of the current web request....

Puma P280730 Stainless Steel IP 4 Star Mini
Price:$24.99 Image: http://thediscountdatabase.info/image.php?id=B000VB1FVG Best deal: http://thediscountdatabase.info/index.php?id=B000VB1FVG Puma 4 - star Stainless Steel Pocket Knife. Carry the best of best for LESS! Packs all the quality of Puma in a tiny easy-to-carry package... a big bargain on top German craftsmanship: Plain edge, 2" stainless steel blade; Stainless steel handle; Lockback mechanism; Puma medallion inlay; 5" overall, 3" closed.; Craftsmanship that can't be beat! Order Today! WARNING: You must be 18 or older to purchase Cutlery. Cutlery cannot be...

Chart update automatically
Sometimes I start a data list to chart, create a chart then add data and the chart automatically updates as I add to the data list. The X axis grows with each entry. Other times, I do what I think is the same thing but the chart lays dormant. I actually have to manually update the series range. Nothing I do seems to make it "automatic". Is there a way to make any chart (new or existing) update automatically when dynamic data is involved? Hi, The chart will update automatically if, - you based the chart on a List (Table in xl2007) - created and used a dynamic named range - i...

Registry question
Hello Group, I have an application that saves some configuration data in the registry system (under HKEY_CURRENT_USER\MyApp\ConfData) of the currently logged on user. This way, every user who runs this app will have his own configuration data. When a user runs for the first time this app, the 'MyApp' registry key will be created along with all its subkeys. Now suppose we have 3 users and all of them have already used the app. We will then have the 'MyApp' subtree created for these 3 users. What I want to know is that when the PC admin wants to uninstall the app, how...

2K7 question
Is there a way in E2K7 to make a particular message always send a message out in plain text format? Is that just an Outlook setting? Thanks! ...

MS CRM 4.0 Rollup 2 error Data ProductInfo.ExistingLanguage is not available
Recently upgraded to CRM 4.0. Details are as follows - CRM is on premise - 60 Outlook clients - 32 bit server w/ SRS and Exchange router on CRM server - 64 bit DB CRM 4.0 upgrade goes well. Server application of Rollup 2 works fine. Most of my 60 Outlook clients are getting a popup upon running the rollup 2 .EXE: "Data ProductInfo.ExistingLanguage is not available" These are all US English installs (language id 1033). This is not a multi-language install. All users were made "local admins" for the purpose of the client upgrade to 4.0 as well as the rollup install. About...

Question #3
Hi I have some data that I graph. Column A has a date [has data in 12 rows] Column B has a data entry (number) [has data in 1st 6 rows] Column C has a data entry (number) [has data in 1 st 6 rows] Column D has a formula that calculates a % on column B & C [formulas in 12 rows] The graph range I have covers the whole 12 rows but it seems to read the formulas in col D and plots every row Is there something I can add to my formula that stops the graph from plotting it unless there has been an answer returned in it? Formula I have is : =IF(B5+C5<1,"",(B5-C5)/B5) Thanks ...

Question about Application behaviour,...
Hi, i have a simple MFC Application, that shows me the System Memory Usage. But after a while the Applications GDI Objects Count grows to something about >8.000 GDI Objects and its Virtual Memory (not much but step by step too) in less than 5 Minutes. I can see with ProcessExplorer that the application does have a high count of page faults. I cant figure out why! The System then starts to react slowly. The Timer invokes every seconds the member function with the code following this text, sets the Position on a progress bar control and raws a transparent text on the surface of the prog...

Defrag question #3
I have a windows 2000 server that's a file/print server and Exchange 2000 also installed and running on the same box. I was wondering what is the best way if I want to run defrag and scandisk on the disk drive? I guess I'm asking because exchange is running on the box and I don't know if I run defrag and scandisk will I run into problems or cause the database problems. Thanks for your help. John In article <4D7A888F-B293-400C-A77C-DC379DDF4BDF@microsoft.com>, John@discussions.microsoft.com says... > I have a windows 2000 server that's a file/print server and Ex...

Page Setup #4
I set up a document for dimensions to be 8x11, save it and close it. The next day I open that same document and find that it has gone back to the default settings of 8.5x11? Would anyone know how I can avoid this risky behavior? -- Chips and Charlie Is your stock actually 8 inches? If it is you must setup your printer for 8 X 11 as well as your page setup. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Chips and Charlie" <ChipsandCharlie@discussions.microsoft.com> wrote in message news:E619DDBA-9FF3-4E26-8D3...

Scrolling chart
http://www.andypope.info/charts/Scrolling.htm I am confused where the reference range SCROLL_DATA and SCROLL_LABELS are set. -- slamm ------------------------------------------------------------------------ slamm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15018 View this thread: http://www.excelforum.com/showthread.php?threadid=382363 In Insert, Name, Define, (the normal place to find a named-range), an also read the Offset Worksheet function in Help for furthe explanation. Hope this help -- Bryan Hesse ---------------------------------------------...

trying to split a stock 1:4
When I were trying to split a stock using Money2000, Money2000 ended up telling me: "The stock split can not be completed due to a lack of disk space." But I still have 18G left on my hard disk. In microsoft.public.money, Wei wrote: >When I were trying to split a stock using Money2000, >Money2000 ended up telling me: "The stock split can not >be completed due to a lack of disk space." But I still >have 18G left on my hard disk. I would try booting into Safe Mode and see if you can do the split. I don't expect it to solve the problem, but it is ea...

ADMT Tool Question
I'm trying to run this tool without success from a Windows 2003 native domain to extract accounts from an NT4 domain. Is this possible? Windows 2003 server Domain: Windows 2003 native mode Forest: Windows 2000 NT4 server sp6a http://www.petri.co.il/active_directory_migration_tool_usage_nt_windows_2003.htm "bobs" <myname@yourdomain.com> wrote in message news:e27m%23Km4FHA.2532@TK2MSFTNGP09.phx.gbl... > I'm trying to run this tool without success from a Windows 2003 native > domain to extract accounts from an NT4 domain. Is this possible? > > Window...

Multi-language support question
We have an MFC application that uses ADO, ATL components to communicate with a SQL Server 2000 database. Our application is currently English only and we are making the conversion to support multiple languages. The text in the application is read in from a table so translating the text itself won't be a big deal programmatically. The text is loaded at log in and we can just autoswitch to the appropriate data based upon who the user is. My concerns are with different character sets, how the different languages impact the OS (how we set the character set in the OS automatically)...

A SQL error has occurred. Going off line with Outlook CRM 4.
I have seen this in multiple workstations. Running the diagnostics, does not report any errors, however removing the temprary data under advenced fixes the problem by deleting the content of the off line database. The problem with this fix is that it may take up to 12 hours for some of our overseas employees to do their initial off line. When is SP1 due? ...

xy Scatter chart #3
I will try to explain this to the best of my knowledge: I have a spreadsheet with some information that is set up like this: Example Risk Impact Probability Director Trls Med 3 1 Wrong Trips High 4 3 Upgrade JAX Low 1 1 These numbers are generated from a series of questions that determine wether the risk is a high, med, or low factor. So there is really no way of knowing what numbers determines a Low, Med, or High Risk The numbers will never go higher than a 4 and lower th...

How do I stop axis titles from overlapping with negative chart da.
The negative bar charts overlap with the category titles. I've tried offset, but it won't let me move the text more than '1000' units, and it still overlaps. Hi, You can find an explanation here. http://www.andypope.info/tips/tip002.htm Cheers Andy KarenNeedsHelp wrote: > The negative bar charts overlap with the category titles. I've tried offset, > but it won't let me move the text more than '1000' units, and it still > overlaps. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Hi Karen - Double click on the category titles o...

4 page publication onto a website
I am trying to put a 4 page publication on to the website so that it can be opened by people who don't have publisher. I managed to do it last month but for can't remember how I did it. When I convert it to a web page it only shows the first page of the publication. Last months issue, when I checked back is a mht.doc but I don't know how I converted the publisher document to this. Can anyone help please? How about converting the Publisher file to a PDF. This way more visitors will have the capability to view it. Many folks here recommend the free primopdf which can be ...

Click on a Pie Chart
Hi, I'm pretty sure this isn't possible but..... I've been asked if it's possible to click (or double click) on a slice of a pie chart and jump to the data it represents (the same as you can in a pivot table). Many thanks Paul I can't speak for 2007, but I recall a few years ago I was looking at making a pie chart clickable to "drill down" in 2003, updating the pie chart to show the component measures of the wedge that was clicked on. It appeared to be possible using VBA, but then the project was terminated before it really started. I can&#...

Previous year data into chart
I currently have a whole table of data imported into Excel from Access. The only problem is, i cannot seem to import the data for the previous year using a query. With the data i would like to create a chart for the past 12 month, i.e the last 12 cells (as the data is taken monthly). Can i select data for the last 12 months that will remain the last 12 months even when the worksheet is refreshed every month? How my data looks: Date % Acknowledged % Investigated 09/2005 85 99 10/2005 ...

How do I add an indicator line to a Gantt chart in excel?
I have created a Gantt chart in Excel. I would like to indicate a specific date - most of the time today's date - by a vertical line showing how many days have passed and how many yet to go for specific tasks. I have attempted to create an 'XY graph' on top of the bar/Gantt chart, but the two do not seem to be compatible. Hi, See my examples of various ways of adding a datum line to a chart. I'm pretty sure one of the techniques should work for you. http://www.andypope.info/charts/averageline.htm Cheers Andy Steve wrote: > I have created a Gantt chart in Excel. I...