Automatically show Standard deviation in a selected range


M

Mike K

Oh Wise Ones,
I like excel's built-in ability to show the rolling
average, count, etc.. in the status bar as you click and drag. Has anyone
ever extended this ability to add standard deviation to the current options
or via a similar method?

Thanks,
Mike

xp pro v2002 sp2
excel 2003
 
Ad

Advertisements

G

Gary Brown

Putting Standard Deviation on your Status Bar would involve creating a new
class.

In your 'ThisWorkbook', establish the class with something like...

'set up class for Stats on the Status Bar
Dim clsStatsOnStatusBar As New Class_StatsOnStatusBar

Then create a Class Module.
In the class module, declare the application with...

'/==================================================/
Public WithEvents App_Stats As Application 'App_Stats is a variable
'/==================================================/


Since you are showing info on the Status Bar, you want to make sure to set
the Status Bar back to normal when exiting, so the Class module needs
something like...

'/==================================================/
Private Sub App_Stats_WorkbookBeforeSave(ByVal WB As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
'return statusbar to 'Ready' prior to saving so that you
' can see the 'Saving' progress bar
Application.StatusBar = False
'
End Sub
'/==================================================/


You want to make sure to show up-to-date information when you first go to a
worksheet, so you need something like...


'/==================================================/
Private Sub App_Stats_SheetActivate(ByVal Sh As Object)
Dim rng As Range

On Error Resume Next

Set rng = Selection

If Selection.Cells.Count < 2 Then
Application.StatusBar = False
GoTo exit_Sub
Else
' Call StatsOnStatusBar(Sh, rng)
Call StatsOnStatusBar(rng)
End If

exit_Sub:
If Err.Number <> 0 Then
Application.StatusBar = False
End If
Set rng = Nothing

End Sub
'/==================================================/



You want to make sure to show up-to-date information when you make changes
to a worksheet, so you need something like...

'/==================================================/
Private Sub App_Stats_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error Resume Next

If Selection.Cells.Count < 2 Then
Application.StatusBar = False
GoTo exit_Sub
Else
' Call StatsOnStatusBar(Sh, Target)
Call StatsOnStatusBar(Target)
End If

exit_Sub:
If Err.Number <> 0 Then
Application.StatusBar = False
End If

End Sub
'/==================================================/


And of course you need the actual information to be put on the Status Bar...

'/==================================================/
Private Sub StatsOnStatusBar(ByVal Target As Range)
Dim strStatusBar As String, strStatus As String

strStatusBar = "My Deviation is: " & Application.WorksheetFunction.StDev
(Selection)
'[don't know if this formula works - untested]

Application.StatusBar = strStatusBar

End Sub
'/==================================================/


Save, get out of Excel and then back in.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top