Sub CalcDeviations()
Dim m As Long
Dim x As Double
Dim y As Double
Dim myRange As Range
Dim v As Variant, s as String
Dim s1 as String, s2 as String
Set myRange = Range("L5", Range("L5").End(xlDown))
s = myRange.Address(1, 1, xlA1)
s1 = Replace(s, "L", "A")
s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")"
v = Evaluate(s2)
x = Application.AveDev(v)
y = Application.StDev(v)

For m = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & m).Value = "Average Deviation" Then
Range("L" & m).FormulaR1C1 = x & "%"
Range("L" & m).NumberFormat = "##0.00%_)"
EseIf Range("A" & m).Value = "Standard Deviation" Then
Range("L" & m).FormulaR1C1 = y & "%"
Range("L" & m).NumberFormat = "##0.00%_)"
End If
Range("L" & m).Select
With Selection.Font
.Bold = True
.Name = "Times New Roman"
.Size = 8
End With
Next

End Sub

worked for me.

--
Regards,
Tom Ogilvy

"Phil Trumpy" wrote:

I have an Access app that uses a crystal report and exports to excel. I was
using this function to get the standard and average deviations for a specific
range once the info was in Excel:

Sub CalcDeviations(objXLApp)
Dim m As Long
Dim x As Double
Dim y As Double
Dim myRange As Range
Set myRange = Range("L5", Range("L5").End(xlDown))
x = objXLApp.WorksheetFunction.AveDev(myRange)
y = objXLApp.WorksheetFunction.StDev(myRange)

For m = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & m).Value = "Average Deviation" Then
Range("L" & m).FormulaR1C1 = x & "%"
Range("L" & m).NumberFormat = "##0.00%_)"
EseIf Range("A" & m).Value = "Standard Deviation" Then
Range("L" & m).FormulaR1C1 = y & "%"
Range("L" & m).NumberFormat = "##0.00%_)"
End If
Range("L" & m).Select
With Selection.Font
.Bold = True
.Name = "Times New Roman"
.Size = 8
End With
Next

End Sub

That worked perfect for me. The problem is that the user changed the
information requested. There are now subtotals in the columns along with the
data to calculate the deviations on. my question is how do I exclude those
subtotals? My condition would be:

If column A contains "RD" or "SFC", do not count those rows when
calculating the deviations. This is my first post, so I apologize if I left
anything out. Thanks in advance for any help.