can I use array formulas in Chart ranges?

G

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
 
D

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

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
 
T

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
 
Top