| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Avoid plotting refferanced blanks as zeros in scatter plot graphs
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 |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot graphs
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 |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot graphs
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 |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot graphs
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 |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot gra
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 |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot gra
Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour. -- David Biddulph "Jobe" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I avoid plotting 'non-empty' cells | Charts and Charting in Excel | |||
| Having trouble plotting blanks on a line graph | Charts and Charting in Excel | |||
| Can I copy x-y scatter plot data direct from one plot to another? | Charts and Charting in Excel | |||
| Plotting Graphs | Excel Discussion (Misc queries) | |||
| plotting graphs | Charts and Charting in Excel | |||
Hybrid Mode
