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