create array of values returned by named formulas

  • Thread starter Thread starter Rob Hick
  • Start date Start date
R

Rob Hick

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
 
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 Where:=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
 
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
 
Back
Top