Junoon
05-05-2012, 05:26 PM
Hi,
I have a sheet which has data like this:
BUCKETS NAD1 REL1 ADD1 TEL1 ALT1 BEL1 FRA1 NAD2 REL2 ADD2 TEL2 ALT2 BEL2 FRA2
1 4 3 2 4 1 2 2 4 2 2 2 1 2 2
1 3 3 2 3 2 3 3 1 2 1 2 2 2 2
1 2 4 2 4 3 2 3 4 5 2 4 3 3 4
1 2 1 1 1 2 1 1 2 2 2 2 2 2 2
2 3 3 2 3 3 2 3 3 4 2 4 3 2 3
2 3 3 2 4 1 2 1 2 1 1 1 2 1 1
2 4 3 2 3 2 2 1 2 5 2 5 1 3 2
I am dumping this sheet into a Multi-dimensional Array (1-D array having 2-D arrays). i want to calculate Means of each column per BUCKET Number 1,2 etc without looping i.e.
Mean= Sum(.Index(.transpose(Arr),0,i))/Count(.Index(.transpose(Arr),0,i))
You will see that the headers are repeating themselves viz., NAD1, NAD2 etc. these are measures for Actions 1, 2 etc. i have another 1-D array which has the Action names in sequential order.
Arr() contains:
Action1 name
Action2 name
Action3 name
Action4 name
Action5 name
etc..
What i want to do is match the Actions numbers (names) to the 1,2 etc of the Measures to display the means in a Result sheet in this way:
NAD REL FRA BEL ALT ADD
Action1 1.2 1.1 2.5 4.3 2.1 2.5
Action2 6.3 5.5 3.2 1.2 1.1 3.2
Action3 7.7 2.2 8.2 1.1 2.1 3.1
Action4 4.3 5.6 3.6 2.7 2.9 1.3
etc..
do you think i would need to define a UDT to get this desired format like this:
Type Means
ActionName as string
NAD as double
CAD as double
ADD as double
ALT as double
REL as double
BEL as double
End Type
Dim ArrMeasure() as Means
How do i put the Action Names & calculate the Means for these Actions for the 6 different Measures and put them into such an Array? SEE ATTACHED SAMPLE FILE which has code written and there is a UDF too to grab Action names, but dont know how to Pass this UDF to a variable.
I have a sheet which has data like this:
BUCKETS NAD1 REL1 ADD1 TEL1 ALT1 BEL1 FRA1 NAD2 REL2 ADD2 TEL2 ALT2 BEL2 FRA2
1 4 3 2 4 1 2 2 4 2 2 2 1 2 2
1 3 3 2 3 2 3 3 1 2 1 2 2 2 2
1 2 4 2 4 3 2 3 4 5 2 4 3 3 4
1 2 1 1 1 2 1 1 2 2 2 2 2 2 2
2 3 3 2 3 3 2 3 3 4 2 4 3 2 3
2 3 3 2 4 1 2 1 2 1 1 1 2 1 1
2 4 3 2 3 2 2 1 2 5 2 5 1 3 2
I am dumping this sheet into a Multi-dimensional Array (1-D array having 2-D arrays). i want to calculate Means of each column per BUCKET Number 1,2 etc without looping i.e.
Mean= Sum(.Index(.transpose(Arr),0,i))/Count(.Index(.transpose(Arr),0,i))
You will see that the headers are repeating themselves viz., NAD1, NAD2 etc. these are measures for Actions 1, 2 etc. i have another 1-D array which has the Action names in sequential order.
Arr() contains:
Action1 name
Action2 name
Action3 name
Action4 name
Action5 name
etc..
What i want to do is match the Actions numbers (names) to the 1,2 etc of the Measures to display the means in a Result sheet in this way:
NAD REL FRA BEL ALT ADD
Action1 1.2 1.1 2.5 4.3 2.1 2.5
Action2 6.3 5.5 3.2 1.2 1.1 3.2
Action3 7.7 2.2 8.2 1.1 2.1 3.1
Action4 4.3 5.6 3.6 2.7 2.9 1.3
etc..
do you think i would need to define a UDT to get this desired format like this:
Type Means
ActionName as string
NAD as double
CAD as double
ADD as double
ALT as double
REL as double
BEL as double
End Type
Dim ArrMeasure() as Means
How do i put the Action Names & calculate the Means for these Actions for the 6 different Measures and put them into such an Array? SEE ATTACHED SAMPLE FILE which has code written and there is a UDF too to grab Action names, but dont know how to Pass this UDF to a variable.