Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
I think this should be easy, but I can't figure it out. Would like
code to average a continuous column that varies in number of cells from month to month. I am try to show an avg on top of the column of active cells? Thanks RM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
One way of many. You can also use a defined name for the range
=AVERAGE(INDIRECT("I2:I"&MATCH(99999,I:I))) insertnamedefinecoli in the refers to box =offset($i$1,1,0,counta($i:$i),1) then =average(coli) -- Don Guillett SalesAid Software wrote in message oups.com... I think this should be easy, but I can't figure it out. Would like code to average a continuous column that varies in number of cells from month to month. I am try to show an avg on top of the column of active cells? Thanks RM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
Or if you really want to do it with code try this. Assumes your data is in
column A starting in row 2 and of unknown length. Average is written to A1 Sub FindLastRowandaverage() Dim LastRow As Long If Range("A65536").Value = "" Then LastRow = Range("A65536").End(xlUp).Row Else LastRow = 65536 End If For x = 2 To LastRow total = Cells(x, 1).Value + total Next Average = total / LastRow Cells(1, 1).Value = Average End Sub Mike " wrote: I think this should be easy, but I can't figure it out. Would like code to average a continuous column that varies in number of cells from month to month. I am try to show an avg on top of the column of active cells? Thanks RM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
OOPS can't do sums now. should read
Average = total / (LastRow-1) "Mike" wrote: Or if you really want to do it with code try this. Assumes your data is in column A starting in row 2 and of unknown length. Average is written to A1 Sub FindLastRowandaverage() Dim LastRow As Long If Range("A65536").Value = "" Then LastRow = Range("A65536").End(xlUp).Row Else LastRow = 65536 End If For x = 2 To LastRow total = Cells(x, 1).Value + total Next Average = total / LastRow Cells(1, 1).Value = Average End Sub Mike " wrote: I think this should be easy, but I can't figure it out. Would like code to average a continuous column that varies in number of cells from month to month. I am try to show an avg on top of the column of active cells? Thanks RM |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA question
On Feb 13, 2:55 pm, "Don Guillett" wrote:
a bit shorter Sub averagecol() lr = Cells(Rows.Count, "i").End(xlUp).Row Cells(1, "i") = Application.Average(Range("i2:i" & lr)) 'Cells(1, "i") = Application.Average(Range("i2:i22")) End Sub If you want to do this for a series of columns, post back. -- Don Guillett SalesAid Software "Mike" wrote in message ... Or if you really want to do it with code try this. Assumes your data is in column A starting in row 2 and of unknown length. Average is written to A1 Sub FindLastRowandaverage() Dim LastRow As Long If Range("A65536").Value = "" Then LastRow = Range("A65536").End(xlUp).Row Else LastRow = 65536 End If For x = 2 To LastRow total = Cells(x, 1).Value + total Next Average = total / LastRow Cells(1, 1).Value = Average End Sub Mike " wrote: I think this should be easy, but I can't figure it out. Would like code to average a continuous column that varies in number of cells from month to month. I am try to show an avg on top of the column of active cells? Thanks RM Thanks soo much to both of y, works great! Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good | Excel Programming | |||
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good you | Excel Programming | |||
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good you | Excel Programming | |||
Good morning or good evening depending upon your location. I want to ask you the most important question of your life. Your joy or sorrow for all eternity depends upon your answer. The question is: Are you saved? It is not a question of how good you | Excel Programming |