Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 104
Default Automatically show Standard deviation in a selected range

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 209
Default Automatically show Standard deviation in a selected range

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


"Mike K" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Standard Deviation Soccerboy83 Excel Discussion (Misc queries) 2 June 16th 09 01:27 AM
Standard deviation Svi Excel Discussion (Misc queries) 5 October 15th 07 10:13 AM
standard deviation freddie mac Excel Programming 1 August 4th 06 05:17 PM
Standard Deviation Stan Banner Excel Worksheet Functions 1 March 11th 06 10:11 PM
Excel Charts: Can I show standard deviation on selected series? Bud Fina Charts and Charting in Excel 1 February 13th 05 06:01 PM


All times are GMT +1. The time now is 04:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"