Home |
Search |
Today's Posts |
#1
|
|||
|
|||
making graphs ignore "blank " cells
Hello:
I have a list of data, but some of the data are invalid and should not be taken into consideration (i.e if the data is zero) so I have a graph that comes from a range, which includes expression just like the following one if(A5=0,"",A5*5+10) if(A6=0,"",A6*5+10) if(A7=0,"",A7*5+10) This expression will make a blank cell in case A5 is zero, however it will affect the result of my X-Y scatter graph. Is there a way to put a character instead of "" so that the graph won't be affected (in other words, I want the graph to think it is simply a blank cell, and to have the same behavior as if the cell was blank. I use excel 2000. thanks... guillermo |
#2
|
|||
|
|||
The plotting is based on data being stored in consecutive columns and rows.
The recommendation is that the main body of data be set up that way and that formula links be created elsewhere in the workbook for the purpose of printing or other layout purpose. Charting then becomes a snap. You can select non-contiguous ranges of cell for plotting puposes by using the Ctrl key, but I do not believe it will solve your particular problem. Hope this is useful |:) "Guillermo" wrote: Hello: I have a list of data, but some of the data are invalid and should not be taken into consideration (i.e if the data is zero) so I have a graph that comes from a range, which includes expression just like the following one if(A5=0,"",A5*5+10) if(A6=0,"",A6*5+10) if(A7=0,"",A7*5+10) This expression will make a blank cell in case A5 is zero, however it will affect the result of my X-Y scatter graph. Is there a way to put a character instead of "" so that the graph won't be affected (in other words, I want the graph to think it is simply a blank cell, and to have the same behavior as if the cell was blank. I use excel 2000. thanks... guillermo |
#3
|
|||
|
|||
=if(A5=0,na(),A5*5+10)
HTH Jason Atlanta, GA -----Original Message----- Hello: I have a list of data, but some of the data are invalid and should not be taken into consideration (i.e if the data is zero) so I have a graph that comes from a range, which includes expression just like the following one if(A5=0,"",A5*5+10) if(A6=0,"",A6*5+10) if(A7=0,"",A7*5+10) This expression will make a blank cell in case A5 is zero, however it will affect the result of my X-Y scatter graph. Is there a way to put a character instead of "" so that the graph won't be affected (in other words, I want the graph to think it is simply a blank cell, and to have the same behavior as if the cell was blank. I use excel 2000. thanks... guillermo . |
#4
|
|||
|
|||
awesome.
thanks, guillermo... Atlanta, GA as well.... "Jason Morin" wrote in message ... =if(A5=0,na(),A5*5+10) HTH Jason Atlanta, GA -----Original Message----- Hello: I have a list of data, but some of the data are invalid and should not be taken into consideration (i.e if the data is zero) so I have a graph that comes from a range, which includes expression just like the following one if(A5=0,"",A5*5+10) if(A6=0,"",A6*5+10) if(A7=0,"",A7*5+10) This expression will make a blank cell in case A5 is zero, however it will affect the result of my X-Y scatter graph. Is there a way to put a character instead of "" so that the graph won't be affected (in other words, I want the graph to think it is simply a blank cell, and to have the same behavior as if the cell was blank. I use excel 2000. thanks... guillermo . |
#5
|
|||
|
|||
yea, because the way I have the data, it is not possible for me to have
everthing consecutively. Jason's suggestion was what I was looking for. but thanks also :) guillermo "CyberTaz" wrote in message ... The plotting is based on data being stored in consecutive columns and rows. The recommendation is that the main body of data be set up that way and that formula links be created elsewhere in the workbook for the purpose of printing or other layout purpose. Charting then becomes a snap. You can select non-contiguous ranges of cell for plotting puposes by using the Ctrl key, but I do not believe it will solve your particular problem. Hope this is useful |:) "Guillermo" wrote: Hello: I have a list of data, but some of the data are invalid and should not be taken into consideration (i.e if the data is zero) so I have a graph that comes from a range, which includes expression just like the following one if(A5=0,"",A5*5+10) if(A6=0,"",A6*5+10) if(A7=0,"",A7*5+10) This expression will make a blank cell in case A5 is zero, however it will affect the result of my X-Y scatter graph. Is there a way to put a character instead of "" so that the graph won't be affected (in other words, I want the graph to think it is simply a blank cell, and to have the same behavior as if the cell was blank. I use excel 2000. thanks... guillermo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you ignore blank cells in formulas | Excel Discussion (Misc queries) | |||
how do you ignore blank cells | Excel Discussion (Misc queries) | |||
Non Blank - Blank Cells???? | Excel Discussion (Misc queries) | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) | |||
Automatic copying data excluding blank cells | Excel Worksheet Functions |