Statistics functions in VBA

N

Neil

Hi All,

I've created a sheet in which I would like to calculate mean and standard
deviation for a user selected range.

I have a number of ranges of data (arranged in columns and of varying
length) which the user may select.

Once the user has selected a range, I'd like them to be able to click a
button to run a macro that will
display the mean and st.dev. (as a string maybe) in the cell immediately
below and to the right of the lowest
cell selected by the user.

Example, the user selects the range (C24:C31), runs the macro and ends up
with the mean and st.dev. in cell D32.

The output I'm after can be achieved using a formula like,
'="The mean is "&ROUND(AVERAGE(C24:C31),1)&" and the standard deviation is
"&ROUND(STDEV(C24:C31),1)'

Any thoughts ?

TIA,

Neil
 
N

Neil

OK, OK

Mental blank got me...

I gather I can use the formula property to input the formula.

I guess the tricky bit is identifying the cell to send the formula to,
based on it being referenced from the bottom cell in the selected range.

Thanks again,

Neil
 
D

Dave Peterson

One way:

Option Explicit
Sub testme()

Dim myRng As Range
Dim DestCell As Range
Dim myStr As String
Dim resp As Long

Set myRng = Selection.Areas(1).Columns(1)

With myRng
Set DestCell = .Cells(.Cells.Count).Offset(1, 1)
End With

resp = vbYes
If IsEmpty(DestCell) = False Then
resp = MsgBox(prompt:="Overwrite: " _
& DestCell.Address(0, 0) & "?", _
Buttons:=vbYesNo)
End If

If resp = vbNo Then
Exit Sub
End If

myStr = "=""The mean is ""&ROUND(AVERAGE(" & _
myRng.Address(0, 0) & _
"),1)&"" and the standard deviation is """ & _
"&ROUND(STDEV(" & _
myRng.Address(0, 0) & "),1)"

DestCell.Formula = myStr

End Sub

It looks at the first column of the the first area (if more than one area or
more than one column is selected).
 
N

Neil

Dave,

I've used the code and it works just fine.

I can see other applications for this type of approach too....

Thanks,

Neil
 
Top