Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
hlp
 
Posts: n/a
Default Skip blank cells in diagrams

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   Report Post  
Barb Reinhardt
 
Posts: n/a
Default

You can include the function NA() in that field and the zero value for the
data won't be displayed.

"hlp" wrote in message
...
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.



  #3   Report Post  
John Mansfield
 
Posts: n/a
Default

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.

  #4   Report Post  
hlp
 
Posts: n/a
Default

Hello John Mansfield & Barb Reinhardt

thank you for your answers.


  #5   Report Post  
Posted to microsoft.public.excel.charting
groj
 
Posts: n/a
Default 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.



  #6   Report Post  
Posted to microsoft.public.excel.charting
Andy Pope
 
Posts: n/a
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.charting
groj
 
Posts: n/a
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.charting
Andy Pope
 
Posts: n/a
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.charting
Jennifer
 
Posts: n/a
Default 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

  #10   Report Post  
Posted to microsoft.public.excel.charting
Bernard Liengme
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
To get a lot of charts that refer to different cells Anderson Lee Charts and Charting in Excel 1 December 28th 04 03:35 PM
Excel Charts and cells sacha Charts and Charting in Excel 1 November 30th 04 03:30 PM


All times are GMT +1. The time now is 09:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"