(vba) loop to get average by column

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
888
Office Version
  1. 2010
Platform
  1. Windows
Hi, I am trying to think a loop to get the average of every column in a dynamic array, at the end of every column, I already write a loop to sum row by row, but still I don't get the way to deduce the loop for columns,
HTML:
Option Explicit
Sub addingarrays()
Dim i As Long, j As Long
    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    For j = 1 To 6
    
    Cells(i, "H").Value = Cells(i, "H").Value + Cells(i, j).Value
    Cells(i, "I").Value = Cells(i, "f").Value - Cells(i, "a").Value
    
    Next j
    Next i
    
    
End Sub
and this is my sheet.
Code:
[TABLE="width: 432"]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT]<colgroup><col width="64" style="width: 48pt;" span="9">[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]<tbody>[TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]A[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]B[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]C[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]D[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]E[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]F[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]G[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]H[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]I[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]1[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]2[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]3[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]4[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]6[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]7[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]8[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]9[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]11[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]57[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]13[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]14[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]15[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]16[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]17[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]18[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]93[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]20[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]22[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]23[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]24[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]129[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]25[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]26[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]28[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]29[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]30[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]165[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]32[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]33[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]34[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]35[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]36[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]201[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]37[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]38[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]39[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]40[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]41[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]42[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]237[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]43[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]44[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]46[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]47[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]48[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]273[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]49[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]50[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]51[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]52[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]53[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]54[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]309[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]55[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]56[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]57[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]58[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]59[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]60[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]345[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT]</tbody>[/TABLE]
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,611
I don't get what you are asking. Do you want to get the average of every cell in the table? So like you want the average of a1, a2, a3,... last row in the a column, b1, b2, b3, last row in the b column, and so on and so forth until it ends with the last row in the g column? If so then do something like this.
Code:
Function myFunction(rng as excel.range)
For each cell in rng
IF cell <> "" Then
TotalValue = TotalValue + cell
C = C + 1
End IF
Next cell
myFunction = TotalValue / C
End Function
Then put the formula in whatever cell you want.
=myFunction(A1:F20)
I used F20 but change that to however long your data range is.
 
Last edited:
Upvote 0

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
888
Office Version
  1. 2010
Platform
  1. Windows
Thanks war. I need to inset in the code, lines that let me get the aveage AT THE END OF EVERY --COLUMN-- and see how the loop work. No to insert formulas. I am practicing looping.
 
Upvote 0

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,611
Oh so you want the average of column A after the last Line of column A. And you want the other columns to do the same thing. That's easy.
Sub myMacro()
lastLine = range("A" & rows.count).row
i = 1
Do While i <= lastLine
sumCol = sumCol + range("A" & i).value
C = C + 1
Loop
Range("A" & lastLine + 1).value = sumCol / C
End Sub
 
Upvote 0

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
888
Office Version
  1. 2010
Platform
  1. Windows
I just tried, and do not work.
 
Upvote 0

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,611
This is a very easy code. You can figure it out. Instead of pushing the Run button to run the macro, press the F8 button so it runs the macro line by line. Each time you press F8, the next line will highlight yellow. The line before it has already been processed. Hover your mouse over the processed code and ensure that all of the values are as expected. Ensure that lastLine is equal to the row number of the last cell in your range. Ensure simPly is adding each time it runs through the next loop.
 
Upvote 0

Forum statistics

Threads
1,190,898
Messages
5,983,451
Members
439,843
Latest member
PlanetFitness

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top