Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
chart question
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. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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- |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
Thanks. How do you refer to the defined name in a spreadsheet?
"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- |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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- |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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$2 1,"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 , 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- |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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$2 1,"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- |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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 , 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- |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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- |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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- |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
In , Andy Pope
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 , 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- |
#14
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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- |
#15
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
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- |
#16
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
chart question
In , Andy Pope
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
horrifyingly difficult excel chart / gantt question... | Charts and Charting in Excel | |||
Chart Property Confusion | Charts and Charting in Excel | |||
Pivot / general chart formatting question | Charts and Charting in Excel | |||
Activating a Chart object | Charts and Charting in Excel | |||
Area Chart Formatting Question | Charts and Charting in Excel |