create array of values returned by named formulas

Hi all,

Is it possible to used named formulas to build an array of values that
are not constants. I know you can use the myArray = {0,1,2,3} to
build an array of constants but can you simulate this effect with
named formulas, e.g. myArray = {myval1, myval2, mval3} where the
values are derived by named formulas, eg myval1 = average(some range).
I've tried using various syntax and the named range is 'accepted'
when entered into the define box but when i come to try and use the
name as a chart reference i get an error.

Thanks
Rob

What you want to do is possible within the quirky rules that guide
named formulas. I suspect a general purpose answer does not exist, but
if you share some more specific information about your
business/application, someone should be able to provide further
guidance.

--
Regards,

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

In article ,
says...
Hi all,

Is it possible to used named formulas to build an array of values that
are not constants. I know you can use the myArray = {0,1,2,3} to
build an array of constants but can you simulate this effect with
named formulas, e.g. myArray = {myval1, myval2, mval3} where the
values are derived by named formulas, eg myval1 = average(some range).
I've tried using various syntax and the named range is 'accepted'
when entered into the define box but when i come to try and use the
name as a chart reference i get an error.

Thanks
Rob

OK Tushar, here's the dilemma:

I have a table containing the percentage of my inventory with
specified age ranges:

Month 0 day 1 day 2 day etc...
July 03 2% 15% 25%
Sept 03 5% 10% 25%
etc....

I want to plot this as a cumulative percentage (an S curve) which will
show the percentage of units with at least some days to expiry. To do
this, i want to create an average of a selected period for each age
range and then add the values succesively to create a cumulative
percentage. The final results may look like this:

Max age Cum Perc
0 2%
1 17%
2 42%
etc...

I have managed to create the averages of a selected range but i can't
create the series as shown above unless i do it on the spreadsheet. I
though it might be something like:

srsCumPerc = Avg0, Avg0 + Avg1, Avg0 + Avg1 + Avg2 etc..

but this won't 'build' an array.

Thanks for your help.

Rob

Tushar Mehta wrote in message news:MPG.19dd0377476004898968c@news-server...
What you want to do is possible within the quirky rules that guide
named formulas. I suspect a general purpose answer does not exist, but
if you share some more specific information about your
business/application, someone should be able to provide further
guidance.

--
Regards,

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

In article ,
says...
Hi all,

Is it possible to used named formulas to build an array of values that
are not constants. I know you can use the myArray = {0,1,2,3} to
build an array of constants but can you simulate this effect with
named formulas, e.g. myArray = {myval1, myval2, mval3} where the
values are derived by named formulas, eg myval1 = average(some range).
I've tried using various syntax and the named range is 'accepted'
when entered into the define box but when i come to try and use the
name as a chart reference i get an error.

Thanks
Rob

Use something like:

Option Explicit
Sub Macro1()
Dim aStr As String, anArr(1 To 10) As String, i As Long
'The first loop below simply adds some test data
For i = 1 To 10
anArr(i) = i
Next i
'The next loop replaces the data in the array _
with the running average for entries 1..i
For i = 2 To 10
anArr(i) = CStr((CDbl(anArr(i - 1)) * (i - 1) _
+ CDbl(anArr(i))) / i)
Next i
aStr = Join(anArr, ",")
ActiveWorkbook.Names.Add Name:="SomeArr", RefersToR1C1:= _
"={" & aStr & "}"
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "=Book3!SomeArr"
End Sub


--
Regards,

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

In article ,
says...
OK Tushar, here's the dilemma:

I have a table containing the percentage of my inventory with
specified age ranges:

Month 0 day 1 day 2 day etc...
July 03 2% 15% 25%
Sept 03 5% 10% 25%
etc....

I want to plot this as a cumulative percentage (an S curve) which will
show the percentage of units with at least some days to expiry. To do
this, i want to create an average of a selected period for each age
range and then add the values succesively to create a cumulative
percentage. The final results may look like this:

Max age Cum Perc
0 2%
1 17%
2 42%
etc...

I have managed to create the averages of a selected range but i can't
create the series as shown above unless i do it on the spreadsheet. I
though it might be something like:

srsCumPerc = Avg0, Avg0 + Avg1, Avg0 + Avg1 + Avg2 etc..

but this won't 'build' an array.

Thanks for your help.

Rob

Tushar Mehta wrote in message news:MPG.19dd0377476004898968c@news-server...
What you want to do is possible within the quirky rules that guide
named formulas. I suspect a general purpose answer does not exist, but
if you share some more specific information about your
business/application, someone should be able to provide further
guidance.

--
Regards,

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

In article ,
says...
Hi all,

Is it possible to used named formulas to build an array of values that
are not constants. I know you can use the myArray = {0,1,2,3} to
build an array of constants but can you simulate this effect with
named formulas, e.g. myArray = {myval1, myval2, mval3} where the
values are derived by named formulas, eg myval1 = average(some range).
I've tried using various syntax and the named range is 'accepted'
when entered into the define box but when i come to try and use the
name as a chart reference i get an error.

Thanks
Rob

Rob -

Tushar's example can help in your case. A clue you gave us is that the
array is accepted in the define names dialog, but doesn't work in the
chart. The chart split from the worksheet's evolutionary tree several
generations ago, and has lost the DNA sequence that recognizes complex
defined names that the worksheet thrives on. In most cases now, I just
put the data in a sheet somewhere, without trying to construct a fragile
defined relationship. I may then define a name that refers to this data.

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

Rob Hick wrote:
OK Tushar, here's the dilemma:

I have a table containing the percentage of my inventory with
specified age ranges:

Month 0 day 1 day 2 day etc...
July 03 2% 15% 25%
Sept 03 5% 10% 25%
etc....

I want to plot this as a cumulative percentage (an S curve) which will
show the percentage of units with at least some days to expiry. To do
this, i want to create an average of a selected period for each age
range and then add the values succesively to create a cumulative
percentage. The final results may look like this:

Max age Cum Perc
0 2%
1 17%
2 42%
etc...

I have managed to create the averages of a selected range but i can't
create the series as shown above unless i do it on the spreadsheet. I
though it might be something like:

srsCumPerc = Avg0, Avg0 + Avg1, Avg0 + Avg1 + Avg2 etc..

but this won't 'build' an array.

Thanks for your help.

Rob

Tushar Mehta wrote in message news:MPG.19dd0377476004898968c@news-server...

What you want to do is possible within the quirky rules that guide
named formulas. I suspect a general purpose answer does not exist, but
if you share some more specific information about your
business/application, someone should be able to provide further
guidance.

--
Regards,

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

In article ,
says...

Hi all,

Is it possible to used named formulas to build an array of values that
are not constants. I know you can use the myArray = {0,1,2,3} to
build an array of constants but can you simulate this effect with
named formulas, e.g. myArray = {myval1, myval2, mval3} where the
values are derived by named formulas, eg myval1 = average(some range).
I've tried using various syntax and the named range is 'accepted'
when entered into the define box but when i come to try and use the
name as a chart reference i get an error.

Thanks
Rob