We help IT Professionals succeed at work.

# EXCEL (XY graphic problem)

on
I am displaying a grafic XY type. To make it easier to explain lets assume the values are:
X   /   Y
2   /   3
4   /   7
8   /   9

In addition I draw a trendline through the dataset to correlate the outcome

The X and Y values are achieved according to more or less complex calculations. The values are changing in intervalls and so the trendline and the results. Unfortuately smoetimes some off the X ore Y values become undefined do to the fact that I divide throug 0 or there is a -LN and so on. In this case the X or Y values are st to #VALUE #ERROR. In the graph this points the appear at point (0,0). And the calculated trendline does not reflect the actual correlation, it is flaud by the point (0,0)
I tried different methodes e.g. the isserror clause:
If iserror than set the corresponding cell to "". But then the whole grafic changes from XY type to linetype.
I also tried in case of an errorvalue to set the value of the cell to 0 and the to impose the function
0.00;-0;;@ which according to the EXCEL help "Hide zero values in selected cells" but the cell is not interpreted to be emty by the grafic programme. It still displays the 0 value on the graph.
Is there any function I can use which interprets error values in a cell as not existing cells in a XY graph???
I would like t
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
Hi,

To repeat your problem, I tried to create an error value by set a formular
=5/0
in a cell and found the y corrdinate for that point is 0: like what you said. Then I tried

Private Sub CommandButton1_Click()
For Each acell In ActiveSheet.UsedRange.Cells
If IsError(acell.Value) Then
acell.Value = ""
End If
Next
End Sub

Every thing became OK: the point was ignored in the chart and the cart type still remain XY(Scatter).
I teste that in Excel97. In Excel2000, there is no such problem at all.

Commented:
The problem with this is that when I apply this function to the worksheet, it replaces the funktion which causes an error with "" a blank field so also the formular disappears.
For one calculation it is ok. but for the following calculation there is no formular in this cell anymore.In case during this following calculation the input numbers would not cause any error this field remains blank.

Im mor after a funktion which tells the XY graph to ignore the cell in case there is an error. Any idea??????

But thanks a lot for your efford in principle it works.
CERTIFIED EXPERT

Commented:
Hi,

I see what you mean....
Why not copy the X-Y coordinates (say in column A and B, start in row 1)to other range (say to column D and E, start in row 1) and based on this range to create the chart? The copy fomular for D1 would be
=IF(ISERROR(A1),"",A1)
CERTIFIED EXPERT

Commented:
Hi,

No, that not works :-)
Commented:
=IF(ISERROR(A1),NA(),A1)

Commented:
for the efford I would like to split the p[oints inbetween jklm and cri, but the lin from cri did the job perfectly, so I have to give the points to him
thanks a lot