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
|
|||
|
|||
Skip blank cells in diagrams
In my series data, the blank is created by an if statement so there is a
calculation here that makes the cell blank. should it still not plot it as zero? I looked where you mention, and I am already set up to plot emplty cells as not plotted. But there are still zeros being plotted. any help? thanks "John Mansfield" wrote: hlp, Click once on your chart. Then, go to the standard toolbar and go to Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps). ---- Regards, John Mansfield http://www.pdbook.com "hlp" wrote: How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. |
#2
|
|||
|
|||
Skip blank cells in diagrams
Hi,
The blank part of your formula should be NA() rather than "". =IF( test , value , NA() ) More information here http://www.andypope.info/charts/brokenlines.htm Cheers Andy groj wrote: In my series data, the blank is created by an if statement so there is a calculation here that makes the cell blank. should it still not plot it as zero? I looked where you mention, and I am already set up to plot emplty cells as not plotted. But there are still zeros being plotted. any help? thanks "John Mansfield" wrote: hlp, Click once on your chart. Then, go to the standard toolbar and go to Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps). ---- Regards, John Mansfield http://www.pdbook.com "hlp" wrote: How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
|
|||
|
|||
Skip blank cells in diagrams
Andy,
If I use "NA()" then it places a "#NA" in the excel spread sheet due to that condition. This defeats the purpose of formating my sheet to leave blanks. By leaving blanks there is less information on the sheet and it looks much more professional/neat. Is there another way? Thanks "Andy Pope" wrote: Hi, The blank part of your formula should be NA() rather than "". =IF( test , value , NA() ) More information here http://www.andypope.info/charts/brokenlines.htm Cheers Andy groj wrote: In my series data, the blank is created by an if statement so there is a calculation here that makes the cell blank. should it still not plot it as zero? I looked where you mention, and I am already set up to plot emplty cells as not plotted. But there are still zeros being plotted. any help? thanks "John Mansfield" wrote: hlp, Click once on your chart. Then, go to the standard toolbar and go to Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps). ---- Regards, John Mansfield http://www.pdbook.com "hlp" wrote: How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#4
|
|||
|
|||
Skip blank cells in diagrams
Hi,
The replies to your other posts contain information on formatting the cells such that the #NA is not displayed. This might be a case where 2 sets of the data are required. One formatted for the chart to work and one for the cell presentation to look professional. No need to duplicate the actual data just use cell references. Cheers Andy groj wrote: Andy, If I use "NA()" then it places a "#NA" in the excel spread sheet due to that condition. This defeats the purpose of formating my sheet to leave blanks. By leaving blanks there is less information on the sheet and it looks much more professional/neat. Is there another way? Thanks "Andy Pope" wrote: Hi, The blank part of your formula should be NA() rather than "". =IF( test , value , NA() ) More information here http://www.andypope.info/charts/brokenlines.htm Cheers Andy groj wrote: In my series data, the blank is created by an if statement so there is a calculation here that makes the cell blank. should it still not plot it as zero? I looked where you mention, and I am already set up to plot emplty cells as not plotted. But there are still zeros being plotted. any help? thanks "John Mansfield" wrote: hlp, Click once on your chart. Then, go to the standard toolbar and go to Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps). ---- Regards, John Mansfield http://www.pdbook.com "hlp" wrote: How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#5
|
|||
|
|||
Skip blank cells in diagrams
Hi, I don't want my blank cells to appear anywhere on my chart. I want it to
ignore the cooresponding cell if the cell has #NA in it. Write now, I have over 10,000 cells, but only 50 have results but the chart is trying to chart the entire workbook. "Andy Pope" wrote: Hi, The blank part of your formula should be NA() rather than "". =IF( test , value , NA() ) More information here http://www.andypope.info/charts/brokenlines.htm Cheers Andy groj wrote: In my series data, the blank is created by an if statement so there is a calculation here that makes the cell blank. should it still not plot it as zero? I looked where you mention, and I am already set up to plot emplty cells as not plotted. But there are still zeros being plotted. any help? thanks "John Mansfield" wrote: hlp, Click once on your chart. Then, go to the standard toolbar and go to Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps). ---- Regards, John Mansfield http://www.pdbook.com "hlp" wrote: How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#6
|
|||
|
|||
Skip blank cells in diagrams
Look at dynamic charts
http://peltiertech.com/Excel/Charts/Dynamics.html http://www.tushar-mehta.com/excel/ne...rts/index.html http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm -------------------------------------------------------------------------------- -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jennifer" wrote in message ... Hi, I don't want my blank cells to appear anywhere on my chart. I want it to ignore the cooresponding cell if the cell has #NA in it. Write now, I have over 10,000 cells, but only 50 have results but the chart is trying to chart the entire workbook. "Andy Pope" wrote: Hi, The blank part of your formula should be NA() rather than "". =IF( test , value , NA() ) More information here http://www.andypope.info/charts/brokenlines.htm Cheers Andy groj wrote: In my series data, the blank is created by an if statement so there is a calculation here that makes the cell blank. should it still not plot it as zero? I looked where you mention, and I am already set up to plot emplty cells as not plotted. But there are still zeros being plotted. any help? thanks "John Mansfield" wrote: hlp, Click once on your chart. Then, go to the standard toolbar and go to Tools - Options - Chart - Plot empty cells as: Not plotted (leave gaps). ---- Regards, John Mansfield http://www.pdbook.com "hlp" wrote: How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Condensing a list/range with blank cells to a new list/range without blanks | KR | Worksheet Functions | 4 | July 5th, 2005 04:23 PM |
Counting blank and filled cells within a range. | greg7468 | General Discussion | 3 | June 28th, 2005 10:41 PM |
How do I skip blank cells when copying over a range of cells? | tawells | General Discussion | 2 | June 7th, 2005 09:36 PM |
keep bllank cells blank | Rudy | Worksheet Functions | 5 | August 4th, 2004 10:42 PM |
Skip blank cells | Frank Kabel | Worksheet Functions | 0 | April 8th, 2004 07:37 AM |