|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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: average, macro, microsoft excel |
Thread Tools | Search this Thread |
|
![]() | ||||
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 |