zviito
Board Regular


Joined: 18 Apr 2002
Posts: 6


Status: Offline

   

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.
MrExcel MVP


Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

   

quote:

---

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.....

---

zviito
Board Regular


Joined: 18 Apr 2002
Posts: 6


Status: Offline

   

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.

TsTom
MrExcel MVP


Joined: 19 Mar 2002
Posts: 2845
Location: Cincinnati, Ohio
Flag:

Status: Offline

   

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
Board Regular


Joined: 18 Apr 2002
Posts: 6


Status: Offline

   

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.

TsTom
MrExcel MVP


Joined: 19 Mar 2002
Posts: 2845
Location: Cincinnati, Ohio
Flag:

Status: Offline

   

Hi

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

quote:

---

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

---