Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike Punko
 
Posts: n/a
Default Removing 0 or none 0 values from graphs

Ok got this to work for Line and bar graphs haven't tried anyothers yet.
The pesky problem of yoru graphs always plotting 0 or none 0 values
(#N/A...) as 0 can be hidden in a very sneeky way. Fish off in yoru chart
put a IF statement that if the value is greater then 0 everything goes as
normal, but if it's False then enter in a value like -100. Then on yoru
graph set your Y-axis Minimun to 0. the -100 Data value will be off the
graph and won't show up. Like I said very sneeky.

If statement used.
=IF(A10,A1,-100)
  #2   Report Post  
Andy Pope
 
Posts: n/a
Default

Hi Mike,

I can see how the actual point would not show when set to -100 and the
minimum y axis value set to zero. But that does not stop the line
between points plummeting to the base line and then re surfacing a bit
further along.
Or are your line charts just points?

Cheers
Andy

Mike Punko wrote:
Ok got this to work for Line and bar graphs haven't tried anyothers yet.
The pesky problem of yoru graphs always plotting 0 or none 0 values
(#N/A...) as 0 can be hidden in a very sneeky way. Fish off in yoru chart
put a IF statement that if the value is greater then 0 everything goes as
normal, but if it's False then enter in a value like -100. Then on yoru
graph set your Y-axis Minimun to 0. the -100 Data value will be off the
graph and won't show up. Like I said very sneeky.

If statement used.
=IF(A10,A1,-100)


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #3   Report Post  
Mike Punko
 
Posts: n/a
Default

You are corect on the line/area just plummeting but it's a lot better then
showing a line at the bottom of the graph. Not to mention with a small
change to the code you can still graph the 0 values but remove the none 0
values.

=IF(ISERROR(VALUE(A1)=0),-100,VALUE(A1))

This will allow you to graph the true 0 values but leave out the none 0
values.


"Andy Pope" wrote:

Hi Mike,

I can see how the actual point would not show when set to -100 and the
minimum y axis value set to zero. But that does not stop the line
between points plummeting to the base line and then re surfacing a bit
further along.
Or are your line charts just points?

Cheers
Andy

Mike Punko wrote:
Ok got this to work for Line and bar graphs haven't tried anyothers yet.
The pesky problem of yoru graphs always plotting 0 or none 0 values
(#N/A...) as 0 can be hidden in a very sneeky way. Fish off in yoru chart
put a IF statement that if the value is greater then 0 everything goes as
normal, but if it's False then enter in a value like -100. Then on yoru
graph set your Y-axis Minimun to 0. the -100 Data value will be off the
graph and won't show up. Like I said very sneeky.

If statement used.
=IF(A10,A1,-100)


--

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
Using Match function with duplicate values in an array Richard Excel Worksheet Functions 3 April 22nd 23 07:45 PM
graphs showing unwanted zero values Zadig Galbaras Excel Discussion (Misc queries) 2 June 1st 05 10:34 PM
Line graphs with null values Darren Hagarty Charts and Charting in Excel 3 January 12th 05 10:02 AM
Removing Hidden But Keep Values Wayne Excel Discussion (Misc queries) 2 December 11th 04 03:05 AM
Removing duplicate values in a column natan Excel Worksheet Functions 2 November 22nd 04 07:48 AM


All times are GMT +1. The time now is 06:51 AM.

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

About Us

"It's about Microsoft Excel"