Defined names in charts

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

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?
 
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
 
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))

--
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
 
Hi Alan,
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?
Click to expand...

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?
Click to expand...

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
 
Back
Top