PDA

View Full Version : Solved: 2 dimensional arrays



scrib3
03-16-2013, 12:25 AM
hey
i can work with 1 dimensional arrays and do most things with multiple dimension arrays but i cant seem to get the write code for this example. ive got 10 returns each of 6 different companies in an array(10,6). i want to calculate the average returns using nested for loops and output each of the 6 averages into a different cell. any help would be much appreciated :)

Aussiebear
03-16-2013, 01:56 AM
Are you averaging each of the ten returns or each of the six companies?

scrib3
03-16-2013, 02:02 AM
im averaging each of the 6 companies, so i want 6 seperate averages

scrib3
03-16-2013, 02:38 AM
when i run it, it sums the averages of the 6 companies and output them into one cell. however i want the averages in seperate cells. thanks in advance!

sassora
03-16-2013, 02:55 AM
hey

I might be missing your intentions here but why don't you use averageif() and avoid VBA?

scrib3
03-16-2013, 03:04 AM
i can do it using averageif() but ive been asked to do it this way and ive tried for too long now :P

scrib3
03-16-2013, 04:51 AM
any help would be much appreciated as i need a solution very urgently. thanks

sassora
03-16-2013, 04:58 AM
Do you have any missing values?

Also what do you have so far? i.e. show the code / file.

scrib3
03-16-2013, 05:01 AM
nope i have all 10 return figures for each company

scrib3
03-16-2013, 05:07 AM
Sub task5()
Dim r_arrays(10, 6) As Double
For j = 1 To 6
For i = 1 To 10
r_arrays(i, j) = Range("B115").Cells(i, j)
Sum = r_arrays(i, j) + Sum
Next i
Next j
variable3 = Sum / 10
Range("B134").Cells(1, j) = variable3
End task

when i run this it sums the 6 averages, how do i get it to output each average on its own?

sassora
03-16-2013, 05:22 AM
Something like this will return the averages in the immediate window:
For companyid = 1 To 6
For returnid = 1 To 10
avcompanyreturns(companyid) = avcompanyreturns(companyid) + companyreturns(companyid, returnid) / 10
Next returnid
Debug.Print companyid; avcompanyreturns(companyid)
Next companyid

scrib3
03-16-2013, 05:28 AM
cheers, how would i go about outputting the values into specific cells :) sorry ive only recently started using vba

p.s how would i alter my code above to give me 6 seperate averages opposed to one?

sassora
03-16-2013, 05:45 AM
I think you were on the right track, have a look at this and see how it works.

Sub task5()

Dim r_arrays(10, 6) As Double
Dim av_array(6) As Double
Dim i As Integer
Dim j As Integer

For j = 1 To 6
For i = 1 To 10
r_arrays(i, j) = Range("B115").Cells(i, j)
av_array(j) = av_array(j) + r_arrays(i, j) / 10
Next i

Range("B134").Cells(1, j) = av_array(j)

Next j

End Sub

scrib3
03-16-2013, 05:48 AM
THANK YOU SOSO MUCH! :D thats what i was missing

snb
03-16-2013, 07:27 AM
Your information is rather limited, but in your case a oneliner suffices:
if the range A1:F10 contains the data:

Sub M_snb()
[A11:F11] = [index(choose(column(A11:F11),average(A1:A10),average(B1:B10),average(C1:C10 ),average(D1:D10),average(E1:E10),average(F1:F10)),)]
End Sub

But using an Excelformula seems to be more appropriate.