Add Average Function to a toolbar with an exclusive icon?

[Guest]

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

 

[Gord Dibben]

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

 

[T. Valko]

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 Autosum>Average. You're then able
to select the range to average.

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

Biff

 

[Bob Phillips]

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)