Computing average over columns with VBA

[db1]

Hi there,

I'm struggling to set a macro to calculate the average value over countries for different variables (i = 1 To 8). The % by country, by variable and by year is on sheet "output" and the average % by variable and by year should be in sheet "Average". Here is a bit of the code I need to work around.


Sub Average_AF()



'
Dim tabcode(1 To 8) As String
Dim Statavg_99 As Double
Dim Statavg_00 As Double
Dim Statavg_01 As Double
Dim Statavg_02 As Double
Dim Statavg_03 As Double
Dim Statavg_04 As Double
Dim Statavg_05 As Double
Dim Statavg_06 As Double
Dim Statavg_07 As Double
Dim Statavg_08 As Double
Dim l As Variant
Dim rng As Range



tabcode(1) = "00_"
tabcode(2) = "01_"
tabcode(3) = "02_"
tabcode(4) = "03_"
tabcode(5) = "04_"
tabcode(6) = "05_"
tabcode(7) = "06_"
tabcode(8) = "02B"

Sheets("Output").Select
Sheets.Add

' RENAME the new sheet
ActiveSheet.Name = "Average"
Myoutput = ActiveSheet.Name


Sheets("Output").Select
Set rng = Range("c3:m220")


Statavg_99 = Application.Average(rng.Columns(2))
Statavg_00 = Application.Average(rng.Columns(2))
Statavg_01 = Application.Average(rng.Columns(3))
Statavg_02 = Application.Average(rng.Columns(4))
Statavg_03 = Application.Average(rng.Columns(5))
Statavg_04 = Application.Average(rng.Columns(6))
Statavg_05 = Application.Average(rng.Columns(7))
Statavg_06 = Application.Average(rng.Columns(8))
Statavg_07 = Application.Average(rng.Columns(9))
Statavg_08 = Application.Average(rng.Columns(10))

For l = 3 To 9

Sheets(Myoutput).Cells(l, 2) = Statavg_99
Sheets(Myoutput).Cells(l, 3) = Statavg_00
Sheets(Myoutput).Cells(l, 4) = Statavg_01
Sheets(Myoutput).Cells(l, 5) = Statavg_02
Sheets(Myoutput).Cells(l, 6) = Statavg_03
Sheets(Myoutput).Cells(l, 7) = Statavg_04
Sheets(Myoutput).Cells(l, 8) = Statavg_05
Sheets(Myoutput).Cells(l, 9) = Statavg_06
Sheets(Myoutput).Cells(l, 10) = Statavg_07
Sheets(Myoutput).Cells(l, 11) = Statavg_08

Next l
End If

' ***
Next i

End Sub



This code gives me the same average l times for each year but I'd like to get an average for each value of the array which correponds to column c in sheet "output".
Any help you could give me would be valuable!

Also, as u see the coding is not very nice, maybe there is a way of looping to get the average for every column instead of doing it manually, I tried using application.index(rng.Columns(n)) but doesn't work, i think I'm not declaring it properly.


Thanks,
j

[BrianMH]

Can you provide an example workbook please?

[db1]

Oh sorry,

Here is an exemple workbook.

Many thanks

[BrianMH]

Don't think I quite understand what you are trying to achieve. Can you give us a detailed description of what you want to achieve?

[db1]

I changed exemple.xlsm to a new sheet with a simple example. I have in sheet "output" 3 countries (1-3) with i variables for each country (00_, 01_, ... , 06_). For each country and for each i I have a % by year (from 1999 To 2008)
I need in a new sheet "average" a table with i rows and years in column with the average % of variable i for all countries (taking only account of non empty cells).

ex:
in sheet "output"
country variable % in 1999 % in 2000
1 01 60 40
1 02 12
1 03 13 16
2 01 54 45
2 02 21 26
2 03 15
2 04 16 31
....

In sheet "average"
variable % in 1991 % in 2000
01 57 28
02 21 19
03 13 15.5
04 31

I hope it's more clear and still available for more clarification.

Thanks Brian !

[BrianMH]

Do you need this as a macro or could you use a pivot table? I think a pivot table would be easier to achieve what you want and you might be able to get a lot more detail out of it. I have attached an example.

[db1]

Thanks for your solution Brian.

I wanted to do it through a macro because it seems quicker and wanted to learn to use vba as it's usefull when repeating the same task many times. Also, I have the data by gender and the difference between gender is the interior.cells.color....how to say to the pivot table that the data is for male because its blue or for female because its red?

So maybe there is no easy function to use for this treatment?

Thanks a lot for your help,
j.

[snb]

You can also use formulae in the sheet.
e.g. the average of values in 2004 on variable "01_"

=SUMPRODUCT((B2:B20="01_")*(H2:H20))/SUMPRODUCT((B2:B20="01_")*(H2:H20<>""))