# (vba) loop to get average by column

#### montecarlo2012

##### Well-known Member
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
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:
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.

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

I just tried, and do not work.

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.

thanks warpiglet

Replies
4
Views
198
Replies
0
Views
185
Replies
22
Views
975
Replies
3
Views
155
Replies
2
Views
187

1,219,958
Messages
6,151,148
Members
451,011
Latest member
Pigdog89

### 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?

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