|
| 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 |
|
#1
May 16th, 2007, 03:59 PM
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
May 16th, 2007, 04:12 PM
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
May 16th, 2007, 04:26 PM
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
May 16th, 2007, 04:38 PM
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
May 16th, 2007, 06:12 PM
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
May 16th, 2007, 06:52 PM
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 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
|
|
#7
May 16th, 2007, 07:15 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot gra
Thank you
"David Biddulph" wrote: Format/ Conditional formatting/ Formula is/ =ISNA(A1) Set font colour to the same as the background colour. -- David Biddulph "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
|
|
#8
May 16th, 2007, 07:29 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot gra
Do I have to do this on a cell by cell basis
"David Biddulph" wrote: Format/ Conditional formatting/ Formula is/ =ISNA(A1) Set font colour to the same as the background colour. -- David Biddulph "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
|
|
#9
May 16th, 2007, 07:41 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot gra
Nevermind, I just had to remove the dollar signs. Thanks againJon, Bernard,
and David "Jobe" wrote: Do I have to do this on a cell by cell basis "David Biddulph" wrote: Format/ Conditional formatting/ Formula is/ =ISNA(A1) Set font colour to the same as the background colour. -- David Biddulph "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
|
|
#10
May 16th, 2007, 07:43 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
Avoid plotting refferanced blanks as zeros in scatter plot gra
Either select all the cells (and ensure that A1 is the active cell when you
enter the formula ), or format the one cell and copy format. -- David Biddulph "Jobe" wrote in message ... Do I have to do this on a cell by cell basis "David Biddulph" wrote: Format/ Conditional formatting/ Formula is/ =ISNA(A1) Set font colour to the same as the background colour. -- David Biddulph "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 | |
|
|
Linear Mode
