Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
Assume you have a data of 12 lines that time sensitive due to the
dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
Use a formula for your X-axis series so that you only populate the cell with
a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message ... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
On Dec 20, 12:18 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: Use a formula for your X-axis series so that you only populate the cell with a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message ... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June?- Hide quoted text - - Show quoted text - Maybe I'm missing something here. I tried using your method in a new worksheet. Assuming my X values are going to be from A1:A12 and my Y values are going from B1:12. Obvoiusly, 12 for the number of months in a year. Your formula of: =IF(ISNA(y_value),NA(),x_value Makes the X value turn "#N/A" if the Y value is "#N/A." Otherwise, it would say the X values. In my case of having only data up to June, my colum of A1:A12 shows A1:A6 with the respective name of the month and A7:A12 showing "#N/A." When I create the chart and link it to the 12 rows, it still shows the 6 "#N/A" following the first 6 months named. I'm wanting the chart to only show categories of January - June. No indiciation of the "#N/A" categories included in the chart: Like this: 5 4 3 2 1 Jan Feb March April May June NOT: 5 4 3 2 1 Jan Feb March April May June #N/A #N/A #N/A #N/A #N/A #N/A |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
Hi,
You can use named ranges to only plot the data points up to the first empty point. See this page on various ways of using named ranges. http://peltiertech.com/Excel/Charts/Dynamics.html Alternatively you could hide those rows, using autofilter. Check that the chart is set to Only plot visible cells. With the chart selected use Tools Options Chart, to check this. Cheers Andy Noob Jedi wrote: Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
What sort of chart are you plotting? I assumed you were using an XY chart
as you were talking about X and Y values? -- David Biddulph "Noob Jedi" wrote in message ... On Dec 20, 12:18 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Use a formula for your X-axis series so that you only populate the cell with a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message ... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June?- Hide quoted text - - Show quoted text - Maybe I'm missing something here. I tried using your method in a new worksheet. Assuming my X values are going to be from A1:A12 and my Y values are going from B1:12. Obvoiusly, 12 for the number of months in a year. Your formula of: =IF(ISNA(y_value),NA(),x_value Makes the X value turn "#N/A" if the Y value is "#N/A." Otherwise, it would say the X values. In my case of having only data up to June, my colum of A1:A12 shows A1:A6 with the respective name of the month and A7:A12 showing "#N/A." When I create the chart and link it to the 12 rows, it still shows the 6 "#N/A" following the first 6 months named. I'm wanting the chart to only show categories of January - June. No indiciation of the "#N/A" categories included in the chart: Like this: 5 4 3 2 1 Jan Feb March April May June NOT: 5 4 3 2 1 Jan Feb March April May June #N/A #N/A #N/A #N/A #N/A #N/A |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
On Dec 20, 2:48 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: What sort of chart are you plotting? I assumed you were using an XY chart as you were talking about X and Y values? -- David Biddulph "Noob Jedi" wrote in message ... On Dec 20, 12:18 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Use a formula for your X-axis series so that you only populate the cell with a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message ... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June?- Hide quoted text - - Show quoted text - Maybe I'm missing something here. I tried using your method in a new worksheet. Assuming my X values are going to be from A1:A12 and my Y values are going from B1:12. Obvoiusly, 12 for the number of months in a year. Your formula of: =IF(ISNA(y_value),NA(),x_value Makes the X value turn "#N/A" if the Y value is "#N/A." Otherwise, it would say the X values. In my case of having only data up to June, my colum of A1:A12 shows A1:A6 with the respective name of the month and A7:A12 showing "#N/A." When I create the chart and link it to the 12 rows, it still shows the 6 "#N/A" following the first 6 months named. I'm wanting the chart to only show categories of January - June. No indiciation of the "#N/A" categories included in the chart: Like this: 5 4 3 2 1 Jan Feb March April May June NOT: 5 4 3 2 1 Jan Feb March April May June #N/A #N/A #N/A #N/A #N/A #N/A- Hide quoted text - - Show quoted text - That is correct. After Googling it a little more, I realized that what I'm trying to do is called a Dynamic Chart. However, much of the tutorials online are a bit confusing. Perhaps you could dumb it down for me? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
My method worked quite happily for me the way I described it. I got X
values where I'd got numbers, and the NA() X values were ignored. It sounds to me like you've got text, rather than dates formatted to show as month names. -- David Biddulph "Noob Jedi" wrote in message ... That is correct. After Googling it a little more, I realized that what I'm trying to do is called a Dynamic Chart. However, much of the tutorials online are a bit confusing. Perhaps you could dumb it down for me? On Dec 20, 2:48 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: What sort of chart are you plotting? I assumed you were using an XY chart as you were talking about X and Y values? "Noob Jedi" wrote in message ... Maybe I'm missing something here. I tried using your method in a new worksheet. Assuming my X values are going to be from A1:A12 and my Y values are going from B1:12. Obvoiusly, 12 for the number of months in a year. Your formula of: =IF(ISNA(y_value),NA(),x_value Makes the X value turn "#N/A" if the Y value is "#N/A." Otherwise, it would say the X values. In my case of having only data up to June, my colum of A1:A12 shows A1:A6 with the respective name of the month and A7:A12 showing "#N/A." When I create the chart and link it to the 12 rows, it still shows the 6 "#N/A" following the first 6 months named. I'm wanting the chart to only show categories of January - June. No indiciation of the "#N/A" categories included in the chart: Like this: 5 4 3 2 1 Jan Feb March April May June NOT: 5 4 3 2 1 Jan Feb March April May June #N/A #N/A #N/A #N/A #N/A #N/A On Dec 20, 12:18 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Use a formula for your X-axis series so that you only populate the cell with a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message ... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June?- Hide quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
On Dec 21, 2:03*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote: My method worked quite happily for me the way I described it. *I got X values where I'd got numbers, and the NA() X values were ignored. *It sounds to me like you've got text, rather than dates formatted to show as month names. -- David Biddulph "Noob Jedi" wrote in message ... That is correct. After Googling it a little more, I realized that what I'm trying to do is called a Dynamic Chart. However, much of the tutorials online are a bit confusing. Perhaps you could dumb it down for me? On Dec 20, 2:48 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: What sort of chart are you plotting? *I assumed you were using an XY chart as you were talking about X and Y values? "Noob Jedi" wrote in message .... Maybe I'm missing something here. I tried using your method in a new worksheet. Assuming my X values are going to be from A1:A12 and my Y values are going from B1:12. Obvoiusly, 12 for the number of months in a year. Your formula of: =IF(ISNA(y_value),NA(),x_value Makes the X value turn "#N/A" if the Y value is "#N/A." Otherwise, it would say the X values. In my case of having only data up to June, my colum of A1:A12 shows A1:A6 with the respective name of the month and A7:A12 showing "#N/A." When I create the chart and link it to the 12 rows, it still shows the 6 "#N/A" following the first 6 months named. I'm wanting the chart to only show categories of January - June. No indiciation of the "#N/A" categories included in the chart: Like this: 5 4 3 2 1 Jan * Feb * March * April * May * June NOT: 5 4 3 2 1 Jan * Feb * March * April * May * June * #N/A * #N/A * #N/A * #N/A #N/A * #N/A On Dec 20, 12:18 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Use a formula for your X-axis series so that you only populate the cell with a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message .... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June?- Hide quoted text -- Hide quoted text - - Show quoted text - Yes, David, they are in text format. Good observation. Thank you for being patient with me, by the way. Andy's link to the dynamic chart tutorial was tremendously helpful. There is one more flaw that the dynamic charting still doesn't seem to get around. Perhaps you would know of a way. The way I have it set up is that the Y values are being automatically calculated. It counts the number of records in a particular month and pools into my table chart with each month listed as column labels and their values in the adjacent column. Now, the Dynamic Chart is great if there have yet to be any values to be added into the empty cell, and results in discluding it's reflection on the chart. However, if I have a formula in that cell that reads as "" to show an empty cell until records are added, the chart assumes that it is NOT an empty cell (because a formula is still in the cell) and attempts to include it in its display. Do you know of a method I could use to avoid that from happening with my charts? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
Replace the "" in the formula by NA().
-- David Biddulph "Noob Jedi" wrote in message ... Yes, David, they are in text format. Good observation. Thank you for being patient with me, by the way. Andy's link to the dynamic chart tutorial was tremendously helpful. There is one more flaw that the dynamic charting still doesn't seem to get around. Perhaps you would know of a way. The way I have it set up is that the Y values are being automatically calculated. It counts the number of records in a particular month and pools into my table chart with each month listed as column labels and their values in the adjacent column. Now, the Dynamic Chart is great if there have yet to be any values to be added into the empty cell, and results in discluding it's reflection on the chart. However, if I have a formula in that cell that reads as "" to show an empty cell until records are added, the chart assumes that it is NOT an empty cell (because a formula is still in the cell) and attempts to include it in its display. Do you know of a method I could use to avoid that from happening with my charts? On Dec 21, 2:03 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: My method worked quite happily for me the way I described it. I got X values where I'd got numbers, and the NA() X values were ignored. It sounds to me like you've got text, rather than dates formatted to show as month names. -- David Biddulph "Noob Jedi" wrote in message ... That is correct. After Googling it a little more, I realized that what I'm trying to do is called a Dynamic Chart. However, much of the tutorials online are a bit confusing. Perhaps you could dumb it down for me? On Dec 20, 2:48 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: What sort of chart are you plotting? I assumed you were using an XY chart as you were talking about X and Y values? "Noob Jedi" wrote in message ... Maybe I'm missing something here. I tried using your method in a new worksheet. Assuming my X values are going to be from A1:A12 and my Y values are going from B1:12. Obvoiusly, 12 for the number of months in a year. Your formula of: =IF(ISNA(y_value),NA(),x_value Makes the X value turn "#N/A" if the Y value is "#N/A." Otherwise, it would say the X values. In my case of having only data up to June, my colum of A1:A12 shows A1:A6 with the respective name of the month and A7:A12 showing "#N/A." When I create the chart and link it to the 12 rows, it still shows the 6 "#N/A" following the first 6 months named. I'm wanting the chart to only show categories of January - June. No indiciation of the "#N/A" categories included in the chart: Like this: 5 4 3 2 1 Jan Feb March April May June NOT: 5 4 3 2 1 Jan Feb March April May June #N/A #N/A #N/A #N/A #N/A #N/A On Dec 20, 12:18 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Use a formula for your X-axis series so that you only populate the cell with a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message ... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel chart categories....
On Dec 21, 11:38*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote: Replace the "" in the formula by NA(). -- David Biddulph "Noob Jedi" wrote in message ... Yes, David, they are in text format. Good observation. Thank you for being patient with me, by the way. Andy's link to the dynamic chart tutorial was tremendously helpful. There is one more flaw that the dynamic charting still doesn't seem to get around. Perhaps you would know of a way. The way I have it set up is that the Y values are being automatically calculated. It counts the number of records in a particular month and pools into my table chart with each month listed as column labels and their values in the adjacent column. Now, the Dynamic Chart is great if there have yet to be any values to be added into the empty cell, and results in discluding it's reflection on the chart. However, if I have a formula in that cell that reads as "" to show an empty cell until records are added, the chart assumes that it is NOT an empty cell (because a formula is still in the cell) and attempts to include it in its display. Do you know of a method I could use to avoid that from happening with my charts? On Dec 21, 2:03 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: My method worked quite happily for me the way I described it. I got X values where I'd got numbers, and the NA() X values were ignored. It sounds to me like you've got text, rather than dates formatted to show as month names. -- David Biddulph "Noob Jedi" wrote in message ... That is correct. After Googling it a little more, I realized that what I'm trying to do is called a Dynamic Chart. However, much of the tutorials online are a bit confusing. Perhaps you could dumb it down for me? On Dec 20, 2:48 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: What sort of chart are you plotting? I assumed you were using an XY chart as you were talking about X and Y values? "Noob Jedi" wrote in message .... Maybe I'm missing something here. I tried using your method in a new worksheet. Assuming my X values are going to be from A1:A12 and my Y values are going from B1:12. Obvoiusly, 12 for the number of months in a year. Your formula of: =IF(ISNA(y_value),NA(),x_value Makes the X value turn "#N/A" if the Y value is "#N/A." Otherwise, it would say the X values. In my case of having only data up to June, my colum of A1:A12 shows A1:A6 with the respective name of the month and A7:A12 showing "#N/A." When I create the chart and link it to the 12 rows, it still shows the 6 "#N/A" following the first 6 months named. I'm wanting the chart to only show categories of January - June. No indiciation of the "#N/A" categories included in the chart: Like this: 5 4 3 2 1 Jan Feb March April May June NOT: 5 4 3 2 1 Jan Feb March April May June #N/A #N/A #N/A #N/A #N/A #N/A On Dec 20, 12:18 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Use a formula for your X-axis series so that you only populate the cell with a number if you have Y-axis values, and otherwise put NA() in there. =IF(ISNA(y_value),NA(),x_value) -- David Biddulph "Noob Jedi" wrote in message ... Assume you have a data of 12 lines that time sensitive due to the dates they would be filled. Each line would be a number for that month. I get the whole, adding NA() to remove the zero so that it does not plot. However, I wanted to take it a step further... Currently, the graph will plot up to the month you are in. Let's assume it is June. As for the X-axis it shows from January to December, but the graph does not plot anything after June due to the NA() function to remove any zero values. If you have 12 lines to signify each month, is it possible so that the categories seen on the X axis as well as what is plotted, to only show from January to June instead of January to Decemeber but stops plotting at June?- Hide quoted text - - Show quoted text - Ok, I haven't quite achieved what I was wanting. I'm going to try to reiterate the problem more clarity: Assume that A1:A12 has the months of January to Decemember, respectively. B1:B12 consist of the various values for the month-end numbers. Using the example of A1 and B1, according to your original formula, it would applicably be =IF(ISNA(B1),NA(),"January" ...or something to that effect. As my original sample obstacle posed was that I only had values up to June assuming the reports were time sensitive and yet to fulfill the numbers in restrained by the fact that, the next months have yet to come. That being said, the sample would look similar to this: X_values A1:A6 are the respective months of January to June. Cells A7:A12 are #N/A Y_values B1:B6 are the Y values. Cells B&:B12 are also #N/A The Result: Charting that would basically NOT plot the Y_values that have #N/A in it's cell. The chart's X_values are used to named to the categories at the bottom and in this case, the months of the year. What you'll see is January to June named on the X axis, and 6 #N/A following it, leaving nothing plotted due to the Y_values of #N/A. What I want: I do not want the 6 #N/A's on the X axis to follow it. In fact, I don't want them included in the chart. All you see is January to June on the X axis until more data is entered for the coming months. My obstacle: Now, Andy's link was tremendously helpful. However, there is one flaw: That example assumes that I am manually entering the Y_values. Whether I leave the cells for X series empty, it doesn't plot the X_axis category of it because there are no values to plot on. You only see January to June. However, if there is ANYTHING in the data cell (Y_values), whether value or formula, it assumes the need for the X category and includes it in the chart. For example, if July was #N/A, then you see values for January to June plotted, with July being empty but the category included in the chart. The problem is, in each cell from B1:B12, there is a formula used to count the data entered that is to be counted for a particular month. Since the cell has a formula in it, the chart assumes the category is needed and continues to attempt to plot it. Let me know what you think. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column chart with 3 categories | Charts and Charting in Excel | |||
Bar chart with Y Axes Categories | Charts and Charting in Excel | |||
Chart Categories | Excel Discussion (Misc queries) | |||
3D chart categories | Charts and Charting in Excel | |||
Badly need help to put four categories in one pie chart | Charts and Charting in Excel |