skip zero

[ibo]

Hi all

I have a bar chart.
I have some sample for my requirements on sheet 4.

Date Sales
4.1.2003 565
4.2.2003 603
4.3.2003 0
4.6.2003 0
4.7.2003 635
4.8.2003 621
4.9.2003 0
4.10.2003 553
4.13.2003 409
4.15.2003 100
4.16.2003 0
4.17.2003 0
4.18.2003 702
4.19.2003 703
4.20.2003 704
4.21.2003 705
4.22.2003 0
4.26.2003 0
4.27.2003 711
4.28.2003 712
4.29.2003 0
5.1.2003 715
5.2.2003 716

Insert /name/define Date=OFFSET(Sheet4!$A$2;0;0;COUNTA(Sheet4!$A:$A)-1)

Insert/name/define Sales=OFFSET(Sheet4!$B$2;0;0;COUNTA(Sheet4!$B:$B)-1)
and source area sales for chart:
Sales series y:Sheet4!Sales
x:Sheet4!Dates.

Is it possible to skip zero on the chart .??

n.b.:I always added everyday record.

As below you can see Jon Peltier solution:

To skip over the zeros in the chart, define another name:

Name: SalesNonzero
RefersTo:
=if(Sales>0,Sales,NA())

But I tried as below but it is not working!!!
Insert /name/define
Date=OFFSET(Sheet4!$A$2;0;0;COUNTA(Sheet4!$A:$A)-1)
Insert/name/define
Sales=OFFSET(Sheet4!$B$2;0;0;COUNTA(Sheet4!$B:$B)-1)
Insert/name/define SalesNonsales==if(Sales>0,Sales,NA())
> > and source area sales for chart:
> > Sales series y:Sheet4!SalesNonsales
> > x:Sheet4!Dates.

I seen again zero valus.....

What is my fault.I couldn 't find and need solution......I find
http://www.meadinkent.co.uk/xlgraphoffset.htm link...But it is only last
two zero's skip........

I tried Debra solution:You can use the NA() function instead of a zero
result:

=IF(B2-A2=0,NA(),B2-A2) ...But couldn't understand.
If you have any time please response to me.

Thanks for advance...
--
Direct access to this group with http://web2news.com
http://web2news.com/?microsoft.public.excel.charting

[Jon Peltier]

Ibo -

I neglected to mention that the use of NA() works for most chart types,
but not for Area charts. Are you making an area chart?

Here's a macro that builds noncontiguous ranges for rows that do not
have zeros in the Sales column:

Sub ElimZerosFromRange()
Dim cel As Range
Dim NewRange1 As Range
Dim NewRange2 As Range
For Each cel In Range("Sales")
If cel <> 0 And Not IsEmpty(cel) Then
If NewRange1 Is Nothing Then
Set NewRange1 = cel
Set NewRange2 = cel.Offset(0, -1)
Else
Set NewRange1 = Union(NewRange1, cel)
Set NewRange2 = Union(NewRange2, cel.Offset(0, -1))
End If
End If
Next
ActiveWorkbook.Names.Add _
Name:="NewSales", _
RefersTo:="=" & ActiveSheet.Name & "!" & NewRange1.Address
ActiveWorkbook.Names.Add _
Name:="NewDates", _
RefersTo:="=" & ActiveSheet.Name & "!" & NewRange2.Address
Set cel = Nothing
Set NewRange1 = Nothing
Set NewRange2 = Nothing
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

[ibo]

You are great...Thanks,thanks,thanks

"Jon Peltier" <jonpe...@yahoo.com> wrote in message
news:3ED7AA6...@yahoo.com...