Sub Average_Range()
Dim i As Long
Dim rng As Range
For i = ActiveCell.Row To 1 Step -1
If Cells(i, ActiveCell.Column).Value = "" Then
i = i + 1
Exit For
End If
Next i
Set rng = Range(Cells(i, ActiveCell.Column), ActiveCell.Offset(-1, 0))
ActiveCell.Formula = "=Average(" & rng.Address(False, False) & ")"
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"T. Valko" wrote in message
...
How about if you wanted the average to appear in the cell of your choice,
to more or less emulate the Autosum average functionality, instead of:

Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)

For example, you select a cell then click AutosumAverage. You're then
able to select the range to average.

Personally, I think going through Autosum is sufficient but I'm just
curious.

Biff

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Never have found such a button.

User could assign a macro to a button if the Autosom dropdown is too
unwieldy.

Sub Average_Range()
Set rng = Selection
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Formula = "=Average(" & rng.Address & ")"
End Sub


Gord Dibben MS Excel MVP

On Mon, 2 Apr 2007 19:02:09 -0700, Jugglertwo
wrote:

A student recently asked if the AVERAGE function could be added to the
toolbar as am icon.
This was a good question. I am familiar with customizing toolbars. Also,
I
know in the newer Excel versions that the AutoSum icon has the varioius
function listed in the dropdown box.
She wanted to add an icon similar to the AutoSum button that she could
exclusively use for the AVERAGE function. I did not see where this was
possible.

Is this possible? Thanks in advance for any assistance.
Jugglertwo