| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
"" isn't a blank, as you're figuring out. It's text, which Excel interprets
as zero. You can't make a chart show a gap in a cell that isn't blank, but if you use NA() instead of "", =IF(<condition>=0,NA(),<formula>) The cell gets a big ugly #N/A error, which is not plotted in a line or XY chart. If the chart has markers connected with lines, there will be no marker where there is #N/A, and the line will be interpolated across where the gap would have been. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brandon" <crimson"underscore"m"at"hotmail.com> wrote in message news  2259E65-5AFA-427B-A5F6-(E-Mail Removed)...> In Excel 2007, I would like my chart to not display chart data for empty > cells. > > On the "Hidden and Empty Cell Settings" I have "Show empty cells as: > Gaps". > I can't seem to get this to work, but my cells aren't empty perse. They > return a formula value: > > =IF(<condition>=0,"",<formula>) > > The cells that are being displayed (as 0) on the chart appear blank in the > spreadsheet. Any ideas? |
|
|
|
|
|||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
Jon,
In your example you said that: =IF(<condition>=0,NA(),<formula>) will cause Excel to interpolate a line across where the chart gap would have been. This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above. Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead. Thanks in advance! - Daniel Ferry Jon Peltier wrote: "" isn't a blank, as you're figuring out. 01-Jul-08 "" isn't a blank, as you're figuring out. It's text, which Excel interprets as zero. You can't make a chart show a gap in a cell that isn't blank, but if you use NA() instead of "", =IF(<condition>=0,NA(),<formula>) The cell gets a big ugly #N/A error, which is not plotted in a line or XY chart. If the chart has markers connected with lines, there will be no marker where there is #N/A, and the line will be interpolated across where the gap would have been. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brandon" <crimson"underscore"m"at"hotmail.com> wrote in message news 2259E65-5AFA-427B-A5F6-(E-Mail Removed)...Previous Posts In This Thread: On Tuesday, July 01, 2008 10:11 PM crimson"underscore"m"at"hotmail.com wrote: Chart displaying "blank" cells? In Excel 2007, I would like my chart to not display chart data for empty cells. On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps". I can't seem to get this to work, but my cells aren't empty perse. They return a formula value: =IF(<condition>=0,"",<formula>) The cells that are being displayed (as 0) on the chart appear blank in the spreadsheet. Any ideas? On Tuesday, July 01, 2008 11:49 PM Jon Peltier wrote: "" isn't a blank, as you're figuring out. "" isn't a blank, as you're figuring out. It's text, which Excel interprets as zero. You can't make a chart show a gap in a cell that isn't blank, but if you use NA() instead of "", =IF(<condition>=0,NA(),<formula>) The cell gets a big ugly #N/A error, which is not plotted in a line or XY chart. If the chart has markers connected with lines, there will be no marker where there is #N/A, and the line will be interpolated across where the gap would have been. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brandon" <crimson"underscore"m"at"hotmail.com> wrote in message news 2259E65-5AFA-427B-A5F6-(E-Mail Removed)...On Wednesday, July 02, 2008 3:04 AM Dave Curtis wrote: Re:Chart displaying "blank" cells? Hi, Use NA() instead of "" which will return a #N'A instead of a empty string. Your chart will not plot the #N/A Dave url:http://www.ureader.com/msg/10296332.aspx On Friday, July 11, 2008 11:45 AM @(E-Mail Removed) wrote: Re:Chart displaying "blank" cells? I have the same problem. I tried using NA() but it displayts #NA as a column in chart. Any suggestion ? On Saturday, July 12, 2008 11:16 AM Jon Peltier wrote: NA() works for line and XY charts. Try "" for column or bar charts. NA() works for line and XY charts. Try "" for column or bar charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "@bhi" <@(E-Mail Removed)> wrote in message news:6CD2F060-CBD2-473A-900F-(E-Mail Removed)... On Saturday, July 12, 2008 12:29 PM @(E-Mail Removed) wrote: I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of them. I tried all 3 (1) NA() i.e #NA (2) "" (3) 0 but it creates bar for all of them. "Jon Peltier" wrote: On Saturday, July 12, 2008 12:53 PM Jon Peltier wrote: It creates a bar, or it creates a space where the bar would go? It creates a bar, or it creates a space where the bar would go? Maybe you should paste your data into a reply. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "@bhi" <@(E-Mail Removed)> wrote in message news:FD13C4CD-BAA4-418B-95A1-(E-Mail Removed)... On Monday, July 14, 2008 8:19 PM bh wrote: Yes it creates space where bar would go for all NA() (#NA) or 0. Yes it creates space where bar would go for all NA() (#NA) or 0. For example I am using similar data same as bellow format (both year and sales will come on runtime but will not be more than 10 values) Year Sales 2001 100 2002 200 2003 300 2004 400 2005 500 so here years and its data can come from 2001 to 2010 depend upon user selected criteria. I want to draw chart only for the which value exist. I am using "Jon Peltier" wrote: On Tuesday, July 15, 2008 7:06 AM Jon Peltier wrote: Are the unwanted cells only at the end of the range? Are the unwanted cells only at the end of the range? Then define a named range that is as long as the number of rows with data. http://peltiertech.com/WordPress/200...ynamic-charts/ http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "@bhi" <(E-Mail Removed)> wrote in message news:1697B473-3894-499E-84FF-(E-Mail Removed)... Submitted via EggHeadCafe - Software Developer Portal of Choice BOOK REVIEW: Silverlight 2 Unleashed / Bugnion [SAMS] http://www.eggheadcafe.com/tutorials...lverlight.aspx |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Jon,
In your example you said that: =IF(<condition>=0,NA(),<formula>) will cause Excel to interpolate a line across where the chart gap would have been. This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above. Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead. Thanks in advance! - Daniel Ferry Jon Peltier wrote: "" isn't a blank, as you're figuring out. 01-Jul-08 "" isn't a blank, as you're figuring out. It's text, which Excel interprets as zero. You can't make a chart show a gap in a cell that isn't blank, but if you use NA() instead of "" =IF(<condition>=0,NA(),<formula> The cell gets a big ugly #N/A error, which is not plotted in a line or XY chart. If the chart has markers connected with lines, there will be no marker where there is #N/A, and the line will be interpolated across where the gap would have been - Jo ------ Jon Peltier, Microsoft Excel MV Tutorials and Custom Solution Peltier Technical Services, Inc. - http://PeltierTech.co ______ "Brandon" <crimson"underscore"m"at"hotmail.com> wrote in message news 2259E65-5AFA-427B-A5F6-(E-Mail Removed)...Previous Posts In This Thread: On Tuesday, July 01, 2008 10:11 PM crimson"underscore"m"at"hotmail.com wrote: Chart displaying "blank" cells? In Excel 2007, I would like my chart to not display chart data for empty cells On the "Hidden and Empty Cell Settings" I have "Show empty cells as: Gaps". I can't seem to get this to work, but my cells aren't empty perse. They return a formula value =IF(<condition>=0,"",<formula> The cells that are being displayed (as 0) on the chart appear blank in the spreadsheet. Any ideas? On Tuesday, July 01, 2008 11:49 PM Jon Peltier wrote: "" isn't a blank, as you're figuring out. "" isn't a blank, as you're figuring out. It's text, which Excel interprets as zero. You can't make a chart show a gap in a cell that isn't blank, but if you use NA() instead of "" =IF(<condition>=0,NA(),<formula> The cell gets a big ugly #N/A error, which is not plotted in a line or XY chart. If the chart has markers connected with lines, there will be no marker where there is #N/A, and the line will be interpolated across where the gap would have been - Jo ------ Jon Peltier, Microsoft Excel MV Tutorials and Custom Solution Peltier Technical Services, Inc. - http://PeltierTech.co ______ "Brandon" <crimson"underscore"m"at"hotmail.com> wrote in message news 2259E65-5AFA-427B-A5F6-(E-Mail Removed)...On Wednesday, July 02, 2008 3:04 AM Dave Curtis wrote: Re:Chart displaying "blank" cells? Hi Use NA() instead of "" which will return a #N'A instead of a empty string Your chart will not plot the #N/ Dav url:http://www.ureader.com/msg/10296332.aspx On Friday, July 11, 2008 11:45 AM @(E-Mail Removed) wrote: Re:Chart displaying "blank" cells? I have the same problem. I tried using NA() but it displayts #NA as a colum in chart. Any suggestion ? On Saturday, July 12, 2008 11:16 AM Jon Peltier wrote: NA() works for line and XY charts. Try "" for column or bar charts. NA() works for line and XY charts. Try "" for column or bar charts - Jo ------ Jon Peltier, Microsoft Excel MV Tutorials and Custom Solution Peltier Technical Services, Inc. - http://PeltierTech.co ______ "@bhi" <@(E-Mail Removed)> wrote in message news:6CD2F060-CBD2-473A-900F-(E-Mail Removed)... On Saturday, July 12, 2008 12:29 PM @(E-Mail Removed) wrote: I tried all 3(1) NA() i.e #NA(2) ""(3) 0but it creates bar for all of them. I tried all 3 (1) NA() i.e #NA (2) "" (3) 0 but it creates bar for all of them. "Jon Peltier" wrote: On Saturday, July 12, 2008 12:53 PM Jon Peltier wrote: It creates a bar, or it creates a space where the bar would go? It creates a bar, or it creates a space where the bar would go? Maybe you should paste your data into a reply. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "@bhi" <@(E-Mail Removed)> wrote in message news:FD13C4CD-BAA4-418B-95A1-(E-Mail Removed)... On Monday, July 14, 2008 8:19 PM bh wrote: Yes it creates space where bar would go for all NA() (#NA) or 0. Yes it creates space where bar would go for all NA() (#NA) or 0. For example I am using similar data same as bellow format (both year and sales will come on runtime but will not be more than 10 values) Year Sales 2001 100 2002 200 2003 300 2004 400 2005 500 so here years and its data can come from 2001 to 2010 depend upon user selected criteria. I want to draw chart only for the which value exist. I am using "Jon Peltier" wrote: On Tuesday, July 15, 2008 7:06 AM Jon Peltier wrote: Are the unwanted cells only at the end of the range? Are the unwanted cells only at the end of the range? Then define a named range that is as long as the number of rows with data. http://peltiertech.com/WordPress/200...ynamic-charts/ http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "@bhi" <(E-Mail Removed)> wrote in message news:1697B473-3894-499E-84FF-(E-Mail Removed)... On Tuesday, December 01, 2009 11:34 PM daniel ferry wrote: Charting Question Jon, In your example you said that: =IF(<condition>=0,NA(),<formula>) will cause Excel to interpolate a line across where the chart gap would have been. This is good functionality, but if I want to have Excel instead actually leave the gap between two markers and NOT interpolate the line, how do I do this. In other words I would like the line chart data series to appear disjointed. The chart series is calculated so there is no problem incorporating formulae such as the above. Also if you would not mind a tangential question, with Excel 2007 is there any way to force an added trend line to appear on the chart visually "behind" the series it is trending, sort of like a z-order or z-index in programming? The default seems to be that it appears on top of the series line and for my use it would be cleaner to have it appear behind instead. Thanks in advance! - Daniel Ferry Submitted via EggHeadCafe - Software Developer Portal of Choice SQL Server - Detach / Attach / Full Text Catalog http://www.eggheadcafe.com/tutorials...tach--att.aspx |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Graphing with "Empty" vs "Blank" Cells | WebColin | Microsoft Excel Discussion | 2 | 23rd Nov 2008 01:36 AM |
| How to count blank cells (Rows) between Non-Blank cells in Col "A" | Dennis | Microsoft Excel Misc | 8 | 21st Jul 2006 12:25 AM |
| How to fill in "BLANK" Cells Automactically...Large Spread Sheet | =?Utf-8?B?bXNidXR0b24yNw==?= | Microsoft Excel Misc | 3 | 15th Jan 2006 05:12 PM |
| Do not want chart to graph cells with formulas that are "blank" | =?Utf-8?B?anVsbm9yZA==?= | Microsoft Excel Charting | 1 | 19th Sep 2005 06:29 AM |
| Each time user clicks on "chart" a "chart tool box" appears.. can I disable? | Kenji | Microsoft Excel Worksheet Functions | 0 | 29th Dec 2003 09:42 PM |
