Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 23-10-2009
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?
Reply With Quote
  #2  
Old 23-10-2009
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
Reply With Quote
  #3  
Old 23-10-2009
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.
Reply With Quote
  #4  
Old 23-10-2009
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
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.