If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
Since worksheets don't cost much, I generally make one table for each use I
have for the data, all linked back to the original data. There could be several sheets: one for the original data, one for the chart source data, one for optimal screen viewing, one (or more) optimized to print, one or more optimized to export to Word or PowerPoint. The chart one shows the #N/A, and it's useful to show these; the ones for display do not. The ones for display might skip rows or columns and have fancy borders or shading to make them easy to read, the one for the chart does not. It's just so much easier to do each table individually than to try to figure out how the same table will work for different functions that have different requirements. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jobe" wrote in message news Thank you guys for your input, NA() does work for not plotting zero values but now my table is is littered with #N/As. Is it possible to have a to eliminate this display of the #N/As from the table. Otherwise I will make a hidden table with NA(). "Bernard Liengme" wrote: Agreed! so use NA() in place of "" in the formula best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Jon Peltier" wrote in message ... If the "blank" is the result of "" in a formula, Tools menu Options Chart tab 'Plot Empty Cells As' will have no effect. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Bernard Liengme" wrote in message ... Replace the blank cell by =NA() or With chart select: Tools | Options |Chart and specify how missing data is to be treated best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Jobe" wrote in message ... I can not figure out how not to plot data that referances blank cells as zero's. I have a worksheet that links to tables with data orginized by size (mm) from big to small. This worksheet contains a scatter plot graph that looks at all sizes in the table. Typically not all sizes have data. The only way I can figure out to remove the zero values from the blank cells is by manualy selecting these cells and clearing the content. I have tried several attempts at differant logic ( if(isnumber(A1),A1,""), and if(isblank(A1),"",A1) ect. and nothing seems to work. I could use conditional formating but I would like to keep things as simple as possible |
#12
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
On Wed, 16 May 2007, in microsoft.public.excel.charting,
David Biddulph said: Format/ Conditional formatting/ Formula is/ =ISNA(A1) Set font colour to the same as the background colour. Alternatively, using the custom number format General;;; also works, as suggested by tmirelle in a previous post http://groups.google.com/group/ microsoft.public.excel.charting/msg/8d3ae44f522983f4 The funny thing is that, now I've tested it, the reason why I never found it by myself is clear. I was testing it on cells, and in cells that format does *not* obscure the #N/A. But it does in the chart. Weird! Also, although it works fine in line and scatter charts, it produced a very strange result in a bar chart. The actual "#N/A" text appeared superimposed on the label of the previous data point, and the label frame associated with that text was way up in the top left hand corner. Very buggy. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#13
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
I just tested this (Excel 2003 SP2) and discovered that
format;;; does not prevent display of #N/A in the chart (data labels showing values of points) nor in the sheet. If what you have is text that looks like the error, such as '#N/A, then the format hides this label in the chart and in the sheet, because the format for text (after the third is blank. This is why NA() works for charts with markers (XY, line, and radar), because points (markers) are not plotted for #N/A (and therefore the labels do not show), while "" works for column or bar charts, because the zero-thickness column/bar doesn't show, and neither does the "" label. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message news On Wed, 16 May 2007, in microsoft.public.excel.charting, David Biddulph said: Format/ Conditional formatting/ Formula is/ =ISNA(A1) Set font colour to the same as the background colour. Alternatively, using the custom number format General;;; also works, as suggested by tmirelle in a previous post http://groups.google.com/group/ microsoft.public.excel.charting/msg/8d3ae44f522983f4 The funny thing is that, now I've tested it, the reason why I never found it by myself is clear. I was testing it on cells, and in cells that format does *not* obscure the #N/A. But it does in the chart. Weird! Also, although it works fine in line and scatter charts, it produced a very strange result in a bar chart. The actual "#N/A" text appeared superimposed on the label of the previous data point, and the label frame associated with that text was way up in the top left hand corner. Very buggy. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#14
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
On Wed, 16 May 2007, in microsoft.public.excel.charting,
Jon Peltier said: This is why NA() works for charts with markers (XY, line, and radar), because points (markers) are not plotted for #N/A (and therefore the labels do not show), while "" works for column or bar charts, because the zero-thickness column/bar doesn't show, and neither does the "" label. I must now slap my forehead as I did when tmirelle first made the suggestion, for the opposite reason this time: that it never did work after all, it just looked like it worked because the points didn't show. It's still funny about the labels and label frames going buggy all over the place with bars for me though. I guess the setup you described in your previous post is the only way to go: mirrored sheets, one formatted for publication as a table, one formatted for feeding a chart, etc. A counterintuitive case of the "helper column" principle for preparing spreadsheet data for charting, in a situation where one wouldn't think it necessary. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#15
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
If 'Keep It Simple' means use an extra sheet, then use an extra sheet. I
can't believe how many people are so reluctant to do so even when the benefits are explained. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Wed, 16 May 2007, in microsoft.public.excel.charting, Jon Peltier said: This is why NA() works for charts with markers (XY, line, and radar), because points (markers) are not plotted for #N/A (and therefore the labels do not show), while "" works for column or bar charts, because the zero-thickness column/bar doesn't show, and neither does the "" label. I must now slap my forehead as I did when tmirelle first made the suggestion, for the opposite reason this time: that it never did work after all, it just looked like it worked because the points didn't show. It's still funny about the labels and label frames going buggy all over the place with bars for me though. I guess the setup you described in your previous post is the only way to go: mirrored sheets, one formatted for publication as a table, one formatted for feeding a chart, etc. A counterintuitive case of the "helper column" principle for preparing spreadsheet data for charting, in a situation where one wouldn't think it necessary. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#16
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
I need some help with the problem. I tried this in my formula which is
=IF(V7+W70,V7+W7,NA()). This does not work for my bar chart. The chart still shoes my value as Zero. Why is that? Mac "Jon Peltier" wrote: Since worksheets don't cost much, I generally make one table for each use I have for the data, all linked back to the original data. There could be several sheets: one for the original data, one for the chart source data, one for optimal screen viewing, one (or more) optimized to print, one or more optimized to export to Word or PowerPoint. The chart one shows the #N/A, and it's useful to show these; the ones for display do not. The ones for display might skip rows or columns and have fancy borders or shading to make them easy to read, the one for the chart does not. It's just so much easier to do each table individually than to try to figure out how the same table will work for different functions that have different requirements. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jobe" wrote in message news Thank you guys for your input, NA() does work for not plotting zero values but now my table is is littered with #N/As. Is it possible to have a to eliminate this display of the #N/As from the table. Otherwise I will make a hidden table with NA(). "Bernard Liengme" wrote: Agreed! so use NA() in place of "" in the formula best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Jon Peltier" wrote in message ... If the "blank" is the result of "" in a formula, Tools menu Options Chart tab 'Plot Empty Cells As' will have no effect. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Bernard Liengme" wrote in message ... Replace the blank cell by =NA() or With chart select: Tools | Options |Chart and specify how missing data is to be treated best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Jobe" wrote in message ... I can not figure out how not to plot data that referances blank cells as zero's. I have a worksheet that links to tables with data orginized by size (mm) from big to small. This worksheet contains a scatter plot graph that looks at all sizes in the table. Typically not all sizes have data. The only way I can figure out to remove the zero values from the blank cells is by manualy selecting these cells and clearing the content. I have tried several attempts at differant logic ( if(isnumber(A1),A1,""), and if(isblank(A1),"",A1) ect. and nothing seems to work. I could use conditional formating but I would like to keep things as simple as possible |
#17
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
On Wed, 16 May 2007, in microsoft.public.excel.charting,
Jon Peltier said: If 'Keep It Simple' means use an extra sheet, then use an extra sheet. I can't believe how many people are so reluctant to do so even when the benefits are explained. I sympathise to some extent; there's a human instinct to thriftiness that doesn't deal with computer "space" very well. An excellent example of this is my company (and other companies) who periodically commit vast numbers of paid company man-minutes every year to the project of clearing old email out of the servers, when the price of computer storage nowadays is such that simply buying more space would be much cheaper. But you can't convince the budget holders of that. And then again, there's an instinct that mimics the reluctance to duplicate records in databases, for fear that the duplicates could fall out of sync. The answer to that is the relational database, that holds one unique source of each bit of information and feeds many instances where that bit needs to be displayed. Users want the table and the graph to directly read the same cell, lest their table and their graph accidentally end up telling different stories. But good spreadsheet design is easily able to achieve the same goal even when cells have been duplicated "unnecessarily": they will eventually all lead back to the ur-source, and there's no inevitable reason why the helper cells should be hard to sense-check for that goal. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#18
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot gra
The NA() or #N/A only prevents plotting of a marker (in an XY, line, or
radar chart). Otherwise it is plotted as a bar of zero thickness, either on the category axis or in its place in the order of stacked series. It is not really a value of zero, as a text entry would be; if the category axis crosses at a Y value other than zero (bad practice for a bar or column chart, by the way), the Excel-interpreted zero value of the text will result in a bar or column from the category axis to zero on the Value axis. The #N/A does not result in such a zero-seeking bar. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mac" wrote in message ... I need some help with the problem. I tried this in my formula which is =IF(V7+W70,V7+W7,NA()). This does not work for my bar chart. The chart still shoes my value as Zero. Why is that? Mac "Jon Peltier" wrote: Since worksheets don't cost much, I generally make one table for each use I have for the data, all linked back to the original data. There could be several sheets: one for the original data, one for the chart source data, one for optimal screen viewing, one (or more) optimized to print, one or more optimized to export to Word or PowerPoint. The chart one shows the #N/A, and it's useful to show these; the ones for display do not. The ones for display might skip rows or columns and have fancy borders or shading to make them easy to read, the one for the chart does not. It's just so much easier to do each table individually than to try to figure out how the same table will work for different functions that have different requirements. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jobe" wrote in message news Thank you guys for your input, NA() does work for not plotting zero values but now my table is is littered with #N/As. Is it possible to have a to eliminate this display of the #N/As from the table. Otherwise I will make a hidden table with NA(). "Bernard Liengme" wrote: Agreed! so use NA() in place of "" in the formula best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Jon Peltier" wrote in message ... If the "blank" is the result of "" in a formula, Tools menu Options Chart tab 'Plot Empty Cells As' will have no effect. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Bernard Liengme" wrote in message ... Replace the blank cell by =NA() or With chart select: Tools | Options |Chart and specify how missing data is to be treated best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Jobe" wrote in message ... I can not figure out how not to plot data that referances blank cells as zero's. I have a worksheet that links to tables with data orginized by size (mm) from big to small. This worksheet contains a scatter plot graph that looks at all sizes in the table. Typically not all sizes have data. The only way I can figure out to remove the zero values from the blank cells is by manualy selecting these cells and clearing the content. I have tried several attempts at differant logic ( if(isnumber(A1),A1,""), and if(isblank(A1),"",A1) ect. and nothing seems to work. I could use conditional formating but I would like to keep things as simple as possible |
|
Thread Tools | |
Display Modes | |
|
|