return "nothing" from UDF

[Rob Kings]

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

[Nick Hodge]

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
[email protected]HIS


"Rob Kings" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Rob Kings]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]HIS
>
>
> "Rob Kings" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[Nick Hodge]

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
[email protected]HIS


"Rob Kings" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> [email protected]HIS
>>
>>
>> "Rob Kings" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>
>

[Rob Kings]

Nick

Any suggestions for the best way to "hide" the other columns? I've never
seen it done very elegantly.

Rob
PS Like your web-site. Must have taken a lot of work

"Nick Hodge" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]HIS
>
>
> "Rob Kings" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>> [email protected]HIS
>>>
>>>
>>> "Rob Kings" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>

[Jerry W. Lewis]

=SLOPE(IF(ISNA(L3:L47),,L3:L47),IF(ISNA(H3:H47),,H3:H47))

array entered (Ctrl-Shift-Enter)

Jerry

"Rob Kings" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > [email protected]HIS
> >
> >
> > "Rob Kings" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >
> >
>
>
>

[Rob Kings]

Jerry

Sorry, that doesn't help. If there is an N/A in the range L3:L47 then the if
returns "" and slope still returns #N/A

Rob

"Jerry W. Lewis" <[email protected]> wrote in message
news:[email protected]...
> =SLOPE(IF(ISNA(L3:L47),,L3:L47),IF(ISNA(H3:H47),,H3:H47))
>
> array entered (Ctrl-Shift-Enter)
>
> Jerry
>
> "Rob Kings" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> > [email protected]HIS
>> >
>> >
>> > "Rob Kings" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >
>> >
>>
>>
>>

[MrShorty]

I don't know the specifics of how you're spreadsheet is setup, but here's how I setup and solved a simplified version of the problem. It's not a single cell formula, but you could always put the intermediate steps in hidden columns or out of the way columns. I'm also assuming that you will return n/a from the UDF in case of an error. Basically it boils down to setting up a weighted regression where the points with error values are weighted 0.
input regression
X, Y, wt, X, Y
0, -1 =if(iserror(b2),0,1), =a2*c2, =if(iserror(b2),0,b2)
1, 0 (columns c-e copied down)
2, =na()
3, 2

Columns C and D become the X matrix in the regression and column e becomes the y matrix. Where Y=n/a, that point will be ignored (weighted 0) in the regression. To return the slope, I used =index(linest(e2:e5,c2:d5,false),1).

Does that help?

[Rob Kings]

Mr Shorty

Thanks for the answer. I'll need to get my head around that, but I'm not
going to be looking at this until next week at the earliest now.

Cheers

Rob
"MrShorty" <[email protected]> wrote in
message news:[email protected]...
>
> I don't know the specifics of how you're spreadsheet is setup, but
> here's how I setup and solved a simplified version of the problem.
> It's not a single cell formula, but you could always put the
> intermediate steps in hidden columns or out of the way columns. I'm
> also assuming that you will return n/a from the UDF in case of an
> error. Basically it boils down to setting up a weighted regression
> where the points with error values are weighted 0.
> input regression
> X, Y, wt, X, Y
> 0, -1 =if(iserror(b2),0,1), =a2*c2, =if(iserror(b2),0,b2)
> 1, 0 (columns c-e copied down)
> 2, =na()
> 3, 2
>
> Columns C and D become the X matrix in the regression and column e
> becomes the y matrix. Where Y=n/a, that point will be ignored
> (weighted 0) in the regression. To return the slope, I used
> =index(linest(e2:e5,c2:d5,false),1).
>
> Does that help?
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile:
> http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=493537
>

[Jerry W. Lewis]

SLOPE() ignores all non-numeric data other than Excel error values.
Therefore what I think you said should not be possible. If that is what is
happening, then you need to give complete information to reproduce the
situation. Also include the version of Excel that you are using.

Jerry

"Rob Kings" wrote:

> Jerry
>
> Sorry, that doesn't help. If there is an N/A in the range L3:L47 then the if
> returns "" and slope still returns #N/A
>
> Rob
>
> "Jerry W. Lewis" <[email protected]> wrote in message
> news:[email protected]...
> > =SLOPE(IF(ISNA(L3:L47),,L3:L47),IF(ISNA(H3:H47),,H3:H47))
> >
> > array entered (Ctrl-Shift-Enter)
> >
> > Jerry
> >
> > "Rob Kings" wrote:
> >
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> > [email protected]HIS
> >> >
> >> >
> >> > "Rob Kings" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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