#1
 KANAN14 Member Join Date: Sep 2009 Posts: 131
Average on variable columns

I am looking for a macro to calculate an average of price, but the problem is that the number of columns is variable.

So basically we need the averaging is done in column G, every 2 columns (I & G & K...) and that until the end of the table. Is this possible? How?
#2
 void Member Join Date: Nov 2008 Posts: 1,192
Re: Average on variable columns

Have you any title to your columns, if so, what are they? Can we find common elements in columns to take into account different from those not taken into account? Example: Tot_A_2008 / Tot_A_2009 / Tot_B_2008 / Tot_B_2009 / ...

From Macro:

Code:
```Dim DerCol as long, c as long, MySom as long, MyNb as long, MyAvg

DerCol = sheets("File1").cells(1,rows(1).cells.count).end(xltoleft).column
For c = 7 to DerCol step 2
MySom = MySom + cells(2,c)
MyNb = MyNb +1
Next c
MyAvg = MySom /MyNb```
#3
 KANAN14 Member Join Date: Sep 2009 Posts: 131
Re: Average on variable columns

I can not understand what you have done. Can you explain me or help me for easy understanding? I mean a simpler solution with an easy explanation would do the trick.
#4
 void Member Join Date: Nov 2008 Posts: 1,192
Re: Average on variable columns

OK, try it, the comments are quite explicit to explain things.

Code:
```Sub Avg()

Dim DerCol As Long, c As Long, MySom As Long, MyNb As Long, MyAvg
As Long, r As Long

DerCol = Sheets("File1").Cells(2, Rows(2).Cells.Count).End(xlToLeft).Column 'retrieves the last column filled on the basis of line 2

For r = 3 To 22 'Loop on the lines whereas the number is fixed
For c = 7 To DerCol Step 2 'Loop column 7 (G) filled with the last jump of 2, it means that the following loop c will be worth 9, then 11, ...
MySom = MySom + Cells(r, c) 'Sum the value of the variable line every 2 column by loop
MyNb = MyNb + 1 'Count the number of column (there is no choice but ...)
Next c 'Goes to the next c
Sheets("File1").Cells(r, 5) = Round(MySom / MyNb, 2)
MySom = 0
MyNb = 0
Next r

For r = 3 To 22 'Loop on the lines whereas the number is fixed
For c = 8 To DerCol Step 2 'Loop column 8 (H) to the last filled with jumps of 2, it means that the following loop c will be worth 9, then 11, ...
MySom = MySom + Cells(r, c) 'Sum the value of the variable line every 2 column by loop
MyNb = MyNb + 1 'Count the number of column (there is no choice but ...)
Next c 'Goes to the next c
Sheets("File1").Cells(r, 6) = Round(MySom / MyNb, 2)
MySom = 0
MyNb = 0
Next r

End Sub```

 Tags:

 Thread Tools Search this Thread Show Printable Version Email this Page Search this Thread: Advanced Search Similar Threads for: "Average on variable columns" Thread Thread Starter Forum Replies Last Post Average salary of a VBA Programmers panda dim Off Topic Chat 4 26-11-2010 12:06 AM Runtime Error 91 : Object Variable or with block variable not set Ryan21 Software Development 2 28-08-2009 07:51 PM What is Global Variable static? Declaring static variable instead of Global variable Reckon Software Development 3 25-04-2009 11:34 AM What is the Average Life of a PC? AngerEyes Hardware Peripherals 4 01-09-2008 07:59 PM Wmiprvse.exe using an average 95% CPU m3pilot Small Business Server 5 31-01-2007 01:21 AM

All times are GMT +5.5. The time now is 08:09 PM.