Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax question: using worksheet functions on a subset of a 3-D ar
I have a 3D array that is (1 to 2, 1 to 24, 1 to 12) first parameter is raw number vs. % second parameter is hour of the day third parameter is 5-min increment (within each hour) I'm taking LAN reports and scraping them to do some bandwidth analysis (I don't have control over the source data). I've got the data populated into my array, representing one data point every 5 minutes. Now I want to run some basic statistics against my array, for example: For hour 14, what was the average percent of all values within that hour? Application.worksheetfunction.average(MyArray,1,14 ) Based on my different (random) attempts at syntax, I always get an error about the number of dimensions, or a type mismatch error. I'v tried various combinations of commas, parans, etc, but no joy. Can anyone help me with the syntax to get this first calculation? From that, I should be able to deduce the syntax pattern and do all the rest. Thank you, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax question: using worksheet functions on a subset of a 3-D ar
Well, one issue is that you dimmed the 3rd parameter from 1 to 12 yet are
referring to 14 in your function, which doesn't exist! Bob Umlas "ker_01" wrote in message ... I have a 3D array that is (1 to 2, 1 to 24, 1 to 12) first parameter is raw number vs. % second parameter is hour of the day third parameter is 5-min increment (within each hour) I'm taking LAN reports and scraping them to do some bandwidth analysis (I don't have control over the source data). I've got the data populated into my array, representing one data point every 5 minutes. Now I want to run some basic statistics against my array, for example: For hour 14, what was the average percent of all values within that hour? Application.worksheetfunction.average(MyArray,1,14 ) Based on my different (random) attempts at syntax, I always get an error about the number of dimensions, or a type mismatch error. I'v tried various combinations of commas, parans, etc, but no joy. Can anyone help me with the syntax to get this first calculation? From that, I should be able to deduce the syntax pattern and do all the rest. Thank you, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax question: using worksheet functions on a subset of a 3-
I was trying to indicate that for parameter 1, use the value of 1; for
parameter 2 use the value of 14 (of 24 hours, e.g. 2pm), and I left the third parameter blank because I wanted my average to include all 5-min increments within the first two conditions. Like I said, the syntax eludes me... :) "Bob Umlas" wrote: Well, one issue is that you dimmed the 3rd parameter from 1 to 12 yet are referring to 14 in your function, which doesn't exist! Bob Umlas "ker_01" wrote in message ... I have a 3D array that is (1 to 2, 1 to 24, 1 to 12) first parameter is raw number vs. % second parameter is hour of the day third parameter is 5-min increment (within each hour) I'm taking LAN reports and scraping them to do some bandwidth analysis (I don't have control over the source data). I've got the data populated into my array, representing one data point every 5 minutes. Now I want to run some basic statistics against my array, for example: For hour 14, what was the average percent of all values within that hour? Application.worksheetfunction.average(MyArray,1,14 ) Based on my different (random) attempts at syntax, I always get an error about the number of dimensions, or a type mismatch error. I'v tried various combinations of commas, parans, etc, but no joy. Can anyone help me with the syntax to get this first calculation? From that, I should be able to deduce the syntax pattern and do all the rest. Thank you, Keith . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax question: using worksheet functions on a subset of a 3-
I think you'll have to loop through the array.
If the array were just two dimensions, you could have excel help you by using application.index. But since =index() only allows 2 dimensions, I think you're stuck adding/counting within the loop. ker_01 wrote: I was trying to indicate that for parameter 1, use the value of 1; for parameter 2 use the value of 14 (of 24 hours, e.g. 2pm), and I left the third parameter blank because I wanted my average to include all 5-min increments within the first two conditions. Like I said, the syntax eludes me... :) "Bob Umlas" wrote: Well, one issue is that you dimmed the 3rd parameter from 1 to 12 yet are referring to 14 in your function, which doesn't exist! Bob Umlas "ker_01" wrote in message ... I have a 3D array that is (1 to 2, 1 to 24, 1 to 12) first parameter is raw number vs. % second parameter is hour of the day third parameter is 5-min increment (within each hour) I'm taking LAN reports and scraping them to do some bandwidth analysis (I don't have control over the source data). I've got the data populated into my array, representing one data point every 5 minutes. Now I want to run some basic statistics against my array, for example: For hour 14, what was the average percent of all values within that hour? Application.worksheetfunction.average(MyArray,1,14 ) Based on my different (random) attempts at syntax, I always get an error about the number of dimensions, or a type mismatch error. I'v tried various combinations of commas, parans, etc, but no joy. Can anyone help me with the syntax to get this first calculation? From that, I should be able to deduce the syntax pattern and do all the rest. Thank you, Keith . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel statistical functions on a subset of an array | Excel Programming | |||
Excel worksheet functions question. | Excel Worksheet Functions | |||
Worksheet name syntax question | Excel Programming | |||
How to enter symbols for subset or element of a subset in Excel? | Excel Worksheet Functions | |||
Easy Syntax question: referring to worksheet by VBA name | Excel Programming |