Remember Me?

#1
 Mike Punko Posts: n/a
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
 Andy Pope Posts: n/a

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
 Mike Punko Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Richard Excel Worksheet Functions 3 April 22nd 23 07:45 PM Zadig Galbaras Excel Discussion (Misc queries) 2 June 1st 05 10:34 PM Darren Hagarty Charts and Charting in Excel 3 January 12th 05 10:02 AM Wayne Excel Discussion (Misc queries) 2 December 11th 04 03:05 AM natan Excel Worksheet Functions 2 November 22nd 04 07:48 AM

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