Rob

You seem to have identified the issues. The #N/A *has* to be used on the
chart. I would set the trend line SLOPE function on the hidden column.

You could 'roll your own' SLOPE function, but that would be hugely more
painful

Dilemma!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Rob Kings" wrote in message
...
Nick

Unfortunately that doesn't really help. If I put a value #N/A then
certainly the point isn't plotted. The line is drawn on the chart skipping
that point and the trendline appears. I can show the equation of the
trendline (in the form Y=MX+C) on the chart.

What I'm wanting to do is to have another sheet with a column showing the
direction of the trendline (up or down) to do this I'm using the worksheet
function SLOPE( ) this is in the form

=SLOPE(L3:L47,H3:H47)

where L3:47 and H3:47 are the ranges from my chart. This should have the
effect of returning the M value (from the equation Y=MX+C) without the
user needing to see the chart. I don't really want to go into all the
details of my workbook and the reasons behind it here, but it does all
make sense.

The presence on the #N/A in the L column, whilst fine from the chart and
trendline, makes the SLOPE function return #N/A

Now, this value (be it #N/A or 0 or null) is from my UDF. Yet if I go to
the cell and delete the formula then it does pretty well what I want. So
it seems that I can't have a UDF that returns a value equivalent to there
being absolutely nothing in the cell.

The only way at the moment I can see is to write my own least squares
function which is "#N/A proof" but that, coupled with all the other usual
hoops through which I'm being made to jump have the affect of turning a
relatively simple bit of work into a programming epic.

Either that, or an extra column one for the chart, and one for the SLOPE
function. But there are 3 charts, so that means 3 more ugly columns of
numbers in what is meant to be a simple tool. Time to go and sleep on it.

Cheers

Rob
All the way over in sunny Essex. England


"Nick Hodge" wrote in message
...
Rob

Certainly you will have to return #N/A to stop the chart line dropping to
zero. Outside of code you would use ISERROR or ISNA to trap this #N/A
returning a value of say, 0...not sure about how your SLOPE construct is
built, but hope that gets you in the correct direction and re-assures you
that you have the right route with the chart with #N/A

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Rob Kings" wrote in message
...
I'm having problems with a User Defined function.

I have a chart (line graph) based upon a column of data calculated by a
UDF. I also have another cell that is the slope of a range of the
column using the SLOPE function.

Now, if for any reason I can't calculate a value in my column (e.g. some
of the parameters are missing) I'm not sure what to do:

1. If I return #N/A (Using CVErr(xlErrNA)) then my graph is OK, but the
SLOPE function also returns #N/A (Though the chart has a trend-line
which does have a slope calculated and displayed)

2. If I return "" then the line drops to zero

3. If I return Null then this is being taken as 0 and once again the
line drops to zero.

4. If I delete the formula from the cell then it appears as blank, and
there is a dis-joint in the line

So, how do I get the chart to appear OK (with no disjoint) without
mucking up SLOPE() and what is the difference between a cell with no
value and a UDF that returns Null (or a zero-length string)

Help. I'm very confused.

Rob