possible have sum and average at bottom?

[Ian Elliott]

Thanks for any help.
I use the sum display at the bottom of the window alot, and recently have
been using the average-is it possible to display both, one right next to each
other?
I right-click to the left and right of it, but if I change, I don't add
another one, just change the one that is there. I go between sum and average
enough to that changing it is a bit of a hassle.
It looks like there is enough space there to have average and sum right next
to each other.
Thanks again.

 

[chemist]

Try "=AVERAGE(range of cells)

 

[Bob Phillips]

No, it is one or the other.

You could always build your own

Option Explicit

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("myCB").Delete
On Error GoTo 0

With Application.CommandBars.Add(Name:="myCB", temporary:=True)
With .Controls.Add(Type:=msoControlButton)
.Caption = "Sum="
.Style = msoButtonCaption
End With
With .Controls.Add(Type:=msoControlEdit)
.Caption = "mySum"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Ave="
.Style = msoButtonCaption
End With
With .Controls.Add(Type:=msoControlEdit)
.Caption = "myAve"
End With
.Visible = True
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Dim cell As Range
Dim nTotal As Double
Dim cItems As Long

For Each cell In Target
If IsNumeric(cell.Value) Then
nTotal = nTotal + cell.Value
cItems = cItems + 1
End If
Next cell

If cItems <> 0 Then
Application.CommandBars("myCB").Controls("mySum").Text = nTotal
Application.CommandBars("myCB").Controls("myAve").Text = nTotal /
cItems
End If
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)