can I use array formulas in Chart ranges?

[G.R. Toro]

Hi,

I would like to enter an array formula (instead of a simply a range) as the
X or Y values in a chart. Is this possible?

Thanks,

Gabriel

 

[Don Guillett]

What kind of array formula. Can you post it?
Usually =offset($a$1,0,0,counta($a:$a),6) idea is used to define a NAME and
the name is used.

 

[G.R. Toro]

For instance, instead of specifying =Sheet1!$A1$:$A$10 as the X or Y series
values, I may want to specify something like

=sqrt(Sheet1!$A1$:$A$10)

or
=10.*Sheet1!$A1$:$A$10

which are valid array formulas

I might even want to specify

indirect(Sheet1!$B1)

where Sheet1!$B1 may contain a range such as Sheet1!$A1$:$A$10.

Is this possible? If so, how?

Thanks,

Gabriel

 

[Don Guillett]

AFAIK NO but these guys are the definitive answer
Go to these for more info

http://www.tushar-mehta.com/
http://peltiertech.com/

 

[Tushar Mehta]

For reasons best known to its designers, XL's charting module will not
accept formulas in the SERIES function but it will accept named
formulas. So, to plot the square root values of all numbers between 1
and the value in A1, create a named formula
SqrtVals =SQRT(ROW(INDIRECT("sheet1!1:"&Sheet1!$A$1)))
Now, create a chart with SqrtVals as the series' y-values.

For how to use named formulas in a chart, see the appropriate sections
(first two links) of
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions