how show sum & avg same time in status bar?

[Guest]

Thanks for any help.
In the status bar of my Excel 2002, I can show the sum of selected cells, or
average of selected cells, or a few more options. Is it possible to show the
sum and average of selected cells at the same time?
Like
Sum=10,000 Average=500
I switch back and forth between the two often enough it would be really
nifty to show both at the same time. There seems to be enough space on the
bar to show.
Thanks again.

 

[Sandy]

As far as I know you can't show both, but here is a marco that can do
it for you...


Paste this code into the sheet level of your visual basic editor in the
excel file you'd like to use it.
You'll need to put this in each sheet level to have it work through the
whole workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
Application.DisplayStatusBar = True
On Error Resume Next
x = CStr("Sum=" & Application.WorksheetFunction.Sum(Selection) & " " &
_
"Average=" & Application.WorksheetFunction.Average(Selection))
Application.StatusBar = x
End Sub



Sandy

 

[Guest]

thanks!

As far as I know you can't show both, but here is a marco that can do
it for you...


Paste this code into the sheet level of your visual basic editor in the
excel file you'd like to use it.
You'll need to put this in each sheet level to have it work through the
whole workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
Application.DisplayStatusBar = True
On Error Resume Next
x = CStr("Sum=" & Application.WorksheetFunction.Sum(Selection) & " " &
_
"Average=" & Application.WorksheetFunction.Average(Selection))
Application.StatusBar = x
End Sub



Sandy

 

[Guest]

thank you very much Sandy!

As far as I know you can't show both, but here is a marco that can do
it for you...


Paste this code into the sheet level of your visual basic editor in the
excel file you'd like to use it.
You'll need to put this in each sheet level to have it work through the
whole workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
Application.DisplayStatusBar = True
On Error Resume Next
x = CStr("Sum=" & Application.WorksheetFunction.Sum(Selection) & " " &
_
"Average=" & Application.WorksheetFunction.Average(Selection))
Application.StatusBar = x
End Sub



Sandy

 

[Gord Dibben]

Sandy/Ian

You can put this code once in the Thisworkbook module as such.........

Private Sub Workbook_SheetSelectionChange(ByVal Sh As _
Object, ByVal Target As Range)
Dim x As String
Application.DisplayStatusBar = True
On Error Resume Next
x = CStr("Sum=" & Application.WorksheetFunction.Sum(Selection) & " " & _
"Average=" & Application.WorksheetFunction.Average(Selection))
Application.StatusBar = x
End Sub

Will work on the active worksheet.


Gord Dibben MS Excel MVP