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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
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: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 |
#4
|
|||
|
|||
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: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 |
#5
|
|||
|
|||
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: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 |
Thread Tools | |
Display Modes | |
|
|