Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 24th 06, 12:26 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 39
Default How do I ignore Graphs zero values.

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.

  #2   Report Post  
Old August 24th 06, 12:35 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 25
Default How do I ignore Graphs zero values.

select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...
I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.



  #3   Report Post  
Old August 24th 06, 12:51 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,489
Default How do I ignore Graphs zero values.

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #4   Report Post  
Old August 24th 06, 01:19 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 39
Default How do I ignore Graphs zero values.

Working thanks - as you said, already tried the first suggestion but as it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

  #5   Report Post  
Old January 30th 07, 03:07 AM posted to microsoft.public.excel.charting
AR AR is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 13
Default How do I ignore Graphs zero values.

I have tried doing a conditional format on the #N/A but it does not seem to
work. What do you need to specifically put in the conditional format. I have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but as it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info



  #6   Report Post  
Old January 30th 07, 03:24 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default How do I ignore Graphs zero values.

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info



  #7   Report Post  
Old January 30th 07, 05:23 AM posted to microsoft.public.excel.charting
AR AR is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 13
Default How do I ignore Graphs zero values.

Jon

I have done this but in an earlier discussion Andy suggested after doing the
formula you: " Use conditional formating to hide the #N/A". THis is the part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info




  #8   Report Post  
Old January 30th 07, 08:20 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 620
Default How do I ignore Graphs zero values.

I suggest you read Jon's answer again. He's told you exactly how to do it.
--
David Biddulph

"AR" wrote in message
...
Jon

I have done this but in an earlier discussion Andy suggested after doing
the
formula you: " Use conditional formating to hide the #N/A". THis is the
part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem
to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?


"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but
as
it
was a formulae, it still showed.


"Andy Pope" wrote:
Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

....


  #9   Report Post  
Old January 30th 07, 04:57 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default How do I ignore Graphs zero values.

Here's Debra Dalgleish's explanation:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"AR" wrote in message
...
Jon

I have done this but in an earlier discussion Andy suggested after doing
the
formula you: " Use conditional formating to hide the #N/A". THis is the
part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem
to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but
as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info






  #10   Report Post  
Old February 1st 07, 01:45 AM posted to microsoft.public.excel.charting
AR AR is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 13
Default How do I ignore Graphs zero values.

Thanks Jon for the How To Conditional Format. I wasn't using Formula Is I was
using Cell Is

"Jon Peltier" wrote:

Here's Debra Dalgleish's explanation:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"AR" wrote in message
...
Jon

I have done this but in an earlier discussion Andy suggested after doing
the
formula you: " Use conditional formating to hide the #N/A". THis is the
part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem
to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but
as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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
Function: Ignore Mimimum and Maximum Values Michael J Excel Worksheet Functions 4 March 6th 06 08:41 PM
how do i sum up a column of time values but ignore negative values Croll New Users to Excel 1 October 11th 05 05:55 PM
How can I break values apart that are in the same cell? Phil Excel Worksheet Functions 3 October 4th 05 03:41 PM
ignore missing values while ploting graph Excel plot Excel Discussion (Misc queries) 2 September 15th 05 06:48 PM
Removing 0 or none 0 values from graphs Mike Punko Charts and Charting in Excel 2 September 9th 05 10:14 PM


All times are GMT +1. The time now is 08:24 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017