Defined names in charts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan - 07 Nov 2003 18:44 GMT

I was messing around with Stephen Bullen's special chart

http://www.bmsltd.co.uk/DLCount/DLCount.asp?file=ChtFrmla.zip

that automatically graphs an equation with no actual x,y's on the
worksheet. I wanted to take it a step further, and use it
automatically graph a function I use in the form of
PIArcVal("807btu15v",x,0,). The function evaluates fine with an =
sign. But using the default Y defined name

=EVALUATE(Line!$B$7&"+x*0")

all I get is a chart with the first x and y=1 (instead of 1900). Now
if I remove the business with... &"+x*0" I get x and y=1900 (yay!),
except I still only get 1 data point.

Question here: What does the &"+x*0" do? Where does the 1 come from?
Why do you suppose I'd only get 1 point?

Reply to this Message

Jon Peltier - 08 Nov 2003 05:02 GMT

Alan -

Hate to break this to you. The Chart component in Excel isn't as smart
as the Worksheet component in terms of interpreting names which are
themselves complicated constructed arrays. I presume your UDF puts this
over the top. I've spent hours before coming to this realization. I've
found that it's a tolerable option to put the calculated array into a
worksheet (a hidden one if you want), and chart this, whether directly,
or using a range name to define whatever subset of it you want to chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

> I was messing around with Stephen Bullen's special chart
>
[quoted text clipped - 14 lines]
> Question here: What does the &"+x*0" do? Where does the 1 come from?
> Why do you suppose I'd only get 1 point?

Reply to this Message

Tushar Mehta - 10 Nov 2003 04:26 GMT

As long as your UDF is written to cater to an input value that is more
than a single value (range containing > 1 cells or an array), you can
then use something along the lines of a name:

CallMyUDF1    =myudf({1,2})
CallMyUDF2    =myudf(Sheet1!$B$1:$B$2)

In the next example, G1 indicates which column relative to col. H
contains data for the chart.
CallMyUDF4    =myudf(OFFSET(Sheet1!$H$1,0,Sheet1!$G$1,COUNTA(OFFSET
(Sheet1!$H:$H,0,Sheet1!$G$1)),1))

In the next example, suppose L1 contains the first x-value to be
plotted, L2 the last, and L3 the 'step' size in calculating intervening
x-values. Then the named formulas below give you data for charting:
First    =Sheet1!$L$1
Last    =Sheet1!$L$2
Step    =Sheet1!$L$3
MyXs    =TRANSPOSE((ROW(OFFSET(Sheet1!$A$1,0,0,(Last-First)/Step+1,1))-1)
*Step+First)
MyYs    =myudf(MyXs)

In the last example, suppose we have the number of points to show
(rather than the step size of the previous example). In this case, the
solution becomes:
First    =Sheet1!$L$1
Last    =Sheet1!$L$2
Count    =Sheet1!$L$4
MyXsWithCount    =(ROW(OFFSET(Sheet1!$A$1,0,0,Count,1))-1)*(Last-First)/
(Count-1)+First
MyYsWithCount    =myudf(TRANSPOSE(MyXsWithCount))

Signature

Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office

Reply to this Message

Stephen Bullen - 17 Nov 2003 16:40 GMT

Hi Alan,

> I was messing around with Stephen Bullen's special chart
>
[quoted text clipped - 13 lines]
>
> Question here: What does the &"+x*0" do?

Some of Excel's functions (particularly the trig functions) don't seem
to let Excel know that the result is an array of values, or don't
evaluate the array of x values correctly. I found that adding the +x*0
to the end of the function to be plotted gives Excel enough of a hint
to ensure that the trig functions evaluate the x values as an array and
not a single point.

> Where does the 1 come from? Why do you suppose I'd only get 1 point?

I would imagine that your function is not written to be able to handle
arrays for its input variables (which is what the defined names give
it), and returns 1 in that case.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

Reply to this Message