Excel Graphs Problem 2

[klmorgan]

I have built a graph on an extract of data and where there are gaps in the data, the line on the graph stops where there is no data and restarts where there is: this is what I want it to do.

However I have another graph where I calculate the field to be graphed using:

=IF(L6>0.01,D6/L6,"&quot however where the result is "" the graph is taking that as a zero and the graph dips to zero.

Also where I apply a trend line to this the trend takes into consideration the unwanted zero in the 2nd example but treats the nul data correctly in the first example

How can I get the second graph right?

Regards

Keith

 

[xlbo]

The problem is using a formula will always mean that the cell is NOT blank and therefore will be treated as a zero value

The only thing I can think of is to do a find/replace "" with (blank)

Rgds
~Geoff~

 

[VulcanJedi]

Not sure if this would help, maybe you could change formula from "" to 0s if necessary, but I think if you goto
TOOLS, OPTIONS, Chart, this activeshart has options to graphs empty cells? i am on XL XP though.

hope this helps? Tranpkp

 

[xlbo]

Dunno 'bout XP but the options on 97 only refer to BLANK cells - whether to graph / leave or interpolate
"" is not a blank cell and therefore the options there don't work for this situation Rgds
~Geoff~

 

[klmorgan]

Yes on 2000 it only has an option for EMPTY cells not plotted and I really need it for Zero

I am now trying to use IF choosing a blank cell as the the option if the criteria is not met but that does not work either

Thanks for your help

Grateful for any other ideas

Keith

 

[xlbo]

You could use code to transport your data which uses formulae to a "clean" area and graph from that:

Sub CreateGraphableData()
With Selection
nRows = .Rows.Count
nCols = .Columns.Count
.Copy Destination:=Sheets("ChartData&quot.Range("A1&quot
End With
Sheets("ChartData&quot.Select
For Each c In Sheets("ChartData&quot.Range(Cells(1, 1), Cells(nRows, nCols))
If c.Text = "" Then
c.ClearContents
Else
End If
Next
End Sub

It's a bit clunky but it works - you need to have your data that you want moving selected beforehand

For multiple sets of data, you would need to amend the cell to copy to and the range(cells(1,1) to avoid overwriting Rgds
~Geoff~

 

[klmorgan]

Thanks for all the help the vb script does the job great and helps broaden my understanding of vb, its pretty narrow at present! I've bought a couple of training cd's and hope to use the long winter nights to get my head around it.

Again thanks this site is a godsend

Keith