A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

create array of values returned by named formulas



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2003, 11:42 AM
Rob Hick
external usenet poster
 
Posts: n/a
Default 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
Ads
  #2  
Old September 25th, 2003, 07:52 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default create array of values returned by named formulas

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

  #3  
Old September 26th, 2003, 11:09 AM
Rob Hick
external usenet poster
 
Posts: n/a
Default create array of values returned by named formulas

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:[email protected]
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

  #4  
Old September 26th, 2003, 09:44 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default create array of values returned by named formulas

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:[email protected]
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


  #5  
Old September 27th, 2003, 04:53 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default create array of values returned by named formulas

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:[email protected]

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.