Average totals of each column

Z

zerro

New Member
Joined
Nov 6, 2008
Messages
44
Hi everyone
What I need is a macro that can do the job below instead of individuly. This is only a small portion of the data and the data
ends at all different levels.
I just need the averages of each individual column if it is possible.
The data cannot be sorted.
The columns do have headers.
Any help would be greatly appreciated.
Thank you.



Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">62</td><td style="text-align: center;">9</td><td style="text-align: center;">17</td><td style="text-align: center;">6</td><td style="text-align: center;">13</td><td style="text-align: center;">10</td><td style="text-align: center;">44</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">63</td><td style="text-align: center;">11</td><td style="text-align: center;">4</td><td style="text-align: center;">2</td><td style="text-align: center;">4</td><td style="text-align: center;">1</td><td style="text-align: center;">1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">64</td><td style="text-align: center;">14</td><td style="text-align: center;">2</td><td style="text-align: center;">3</td><td style="text-align: center;">8</td><td style="text-align: center;">8</td><td style="text-align: center;">28</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">65</td><td style="text-align: center;">8</td><td style="text-align: center;">1</td><td style="text-align: center;">18</td><td style="text-align: center;">8</td><td style="text-align: center;">8</td><td style="text-align: center;">29</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">66</td><td style="text-align: center;">1</td><td style="text-align: center;">1</td><td style="text-align: center;">3</td><td style="text-align: center;">6</td><td style="text-align: center;">1</td><td style="text-align: center;">16</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">67</td><td style="text-align: center;">8</td><td style="text-align: center;">2</td><td style="text-align: center;">22</td><td style="text-align: center;">2</td><td style="text-align: center;">28</td><td style="text-align: center;">5</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">68</td><td style="text-align: center;">15</td><td style="text-align: center;">4</td><td style="text-align: center;">5</td><td style="text-align: center;">19</td><td style="text-align: center;">7</td><td style="text-align: center;">27</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">69</td><td style="text-align: center;">3</td><td style="text-align: center;">50</td><td style="text-align: center;">10</td><td style="text-align: center;">2</td><td style="text-align: center;">7</td><td style="text-align: center;">6</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">70</td><td style="text-align: center;">17</td><td style="text-align: center;">2</td><td style="text-align: center;">10</td><td style="text-align: center;">27</td><td style="text-align: center;">12</td><td style="text-align: center;">1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">71</td><td style="text-align: center;">28</td><td style="text-align: center;">8</td><td style="text-align: center;">8</td><td style="text-align: center;">13</td><td style="text-align: center;">3</td><td style="text-align: center;">6</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">72</td><td style="text-align: center;">3</td><td style="text-align: center;">2</td><td style="text-align: center;">9</td><td style="text-align: center;">6</td><td style="text-align: center;">2</td><td style="text-align: center;">3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">73</td><td style="text-align: center;">14</td><td style="text-align: center;">8</td><td style="text-align: center;">9.68056</td><td style="text-align: center;">9.73611</td><td style="text-align: center;">1</td><td style="text-align: center;">13</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">74</td><td style="text-align: center;">14</td><td style="text-align: center;">8</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">12</td><td style="text-align: center;">9.61644</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">75</td><td style="text-align: center;">1</td><td style="text-align: center;">2</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">1</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">76</td><td style="text-align: center;">16</td><td style="text-align: center;">12</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">18</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">77</td><td style="text-align: center;">29</td><td style="text-align: center;">20</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">10</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">78</td><td style="text-align: center;">5</td><td style="text-align: center;">2</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">12</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">79</td><td style="text-align: center;">11</td><td style="text-align: center;">7</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">3</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">80</td><td style="text-align: center;">13</td><td style="text-align: center;">7</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">7</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">81</td><td style="text-align: center;">6</td><td style="text-align: center;">9</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">15</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">82</td><td style="text-align: center;">1</td><td style="text-align: center;">8.49383</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">6</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">83</td><td style="text-align: center;">26</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">1</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">84</td><td style="text-align: center;">8.26506</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">10</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">85</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">1</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">86</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">6</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">87</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">1</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">88</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">2</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">89</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">4</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">90</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">10</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">91</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">1</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">92</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">2</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">93</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">2</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">94</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">3</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">95</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">8</td><td style="text-align: center;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">96</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">7.03191</td><td style="text-align: center;">
</td></tr></tbody></table>
Results​


<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" cellpadding="2.5px" rules="all" width="85%"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" cellpadding="2.5px" rules="all" width="100%"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">I70</th><td style="text-align: left;">=AVERAGE(I1:I69)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">C73</th><td style="text-align: left;">=AVERAGE(C1:C72)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">D73</th><td style="text-align: left;">=AVERAGE(D1:D72)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">J73</th><td style="text-align: left;">=AVERAGE(J1:J72)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">F74</th><td style="text-align: left;">=AVERAGE(F1:F73)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">H81</th><td style="text-align: left;">=AVERAGE(H1:H80)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">B82</th><td style="text-align: left;">=AVERAGE(B1:B81)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">A84</th><td style="text-align: left;">=AVERAGE(A1:A83)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">G87</th><td style="text-align: left;">=AVERAGE(G1:G86)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">K92</th><td style="text-align: left;">=AVERAGE(K1:K91)</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">E96</th><td style="text-align: left;">=AVERAGE(E2:E95)</td></tr></tbody></table></td></tr></tbody></table>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Try

Code: 
Sub ColTtl()
Dim LC As Long, LR As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
    LR = Cells(Rows.Count, j).End(xlUp).Row
    Cells(LR + 1, j).Value = WorksheetFunction.Average(Range(Cells(2, j), Cells(LR, j)))
Next j
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
122
hagia_sofia
hagia_sofia
E
  • Question Question
VBA code clean up
Replies
9
Views
574
mumps
mumps
S
  • Solved
Cell Formatting with Text Data
Replies
2
Views
70
SBulinski1975
S
F
  • Question Question
circular reference - query
Replies
8
Views
282
fayez_MrExcel
F
M
  • Question Question
SLA Time Calculation
Replies
5
Views
355
felixstraube
F

Forum statistics

Threads
1,224,552
Messages
6,179,486
Members
452,917
Latest member
MrsMSalt

Share this page

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
Back
Top