arrays

[zviito]

This is a two part question

I have a 2D array, say TheArray(1 to 365, 1 to 1000) say 365 days in year and 1000 trees. The data input is the number of fallen leaves. I would like to:-

1 - perform calculations on the data in rows. eg say row 1 is january first would like to find average number of fallen leaves.

2 - would like to put data from array into a chart/histogram, but not sure how to do that without actually printing the whole array into a worksheet and then using cell references (which I feel is lenghty)

thanx
zviito

p.s the tree and leaf thing is just an example I am not really doing this.....

[Mark W.]

On 2002-04-19 02:25, zviito wrote:
This is a two part question

I have a 2D array, say TheArray(1 to 365, 1 to 1000) say 365 days in year and 1000 trees. The data input is the number of fallen leaves. I would like to:-

1 - perform calculations on the data in rows. eg say row 1 is january first would like to find average number of fallen leaves.

2 - would like to put data from array into a chart/histogram, but not sure how to do that without actually printing the whole array into a worksheet and then using cell references (which I feel is lenghty)

thanx
zviito

p.s the tree and leaf thing is just an example I am not really doing this.....

I saw your original posting yesterday... and, couldn't understand your aversion to entering the data onto a worksheet. That being the case... why aren't you using a statisical package?

[zviito]

well, I am new to vba, I am only just learning how to use it (am student). The data that I have put into the array is from an iterative process so its maximum size is actually about 365 X 10000 (number of iterations).

as you can see this makes for a very large worksheet. I have put the data into a worksheet, and then manipulated it from there. I just wondered if there is an easier way to do it by just calling the data from the array without having to display it first.

I am going to try and use a for next loop on the data in the array this weekend and see how it goes.

[Tom Schreiner]

Hi
Where is your array coming from?
From what type of file?
Obviously too many elements for one worksheet...
Seems to be an easy problem if you might care to post a little more details?
Thanks,
Tom

[zviito]

Thank you Tom.

Well the Array as I said before is 2D. Its maximum size is 365 (days) by 10 000 (trials/iterations)
The array is actually the result of a random number generator (using the Rnd() function in VBA). I am doing some statisitical analysis. To answer a previous question, I am not using a statistical package cause I only have a standard computer with the standard office packages (unless there is a statistical programme that I am not aware of).

1 - performing calculations on the data in rows. eg say row 1 is january first would like to find average number for January.

2 - would like to put data from array into a chart/histogram. I have just learnt how to draw a chart. (recorded a macro then studied the code) so I think if I manage to do step 1, I may then print the chart data onto a worksheet instead of the whole array, then go from there.

I was going to try and use a for next loop on the data in the array this weekend and see how it goes, but I haven't quite finised it.

[Tom Schreiner]

Hi

You need a simple average for each of the 365 elements?

Sub Avg()
Dim TheDate As Date
Dim ThisDateAvg, e1, e2, DayTotal
Dim MyVeryLargeArray(1 To 365, 1 To 10000)
'assumes MyVeryLargeArray is loaded
For e1 = 1 To 365
DayTotal = 0
For e2 = 1 To 10000
DayTotal = DayTotal + MyVeryLargeArray(e1, e2)
Next
ThisDateAvg = DayTotal / 10000
TheDate = #12/31/2000#
TheDate = TheDate + e1
Range("A" & e1).Value = TheDate
Range("B" & e1).Value = ThisDateAvg
Next
End Sub

Tom