Conditional STDEV formula

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Thanks for the reply Tom. I can see basically what your code is attempting
to do, however, I get the same result as before. I am not sure I get this
line:

s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")"

I noticed that you had written "SPC" instead of "SFC" in your code. Not a
big deal, but when I copied your code in, I forgot to change it and got the
same result as earlier. After I changed it to "SFC", again, same result.
So, I am assuming that this line needs to be changed, but I don't quite
understand what to change it too. Thanks again.

Phil

 

[Tom Ogilvy]

Before I posted, It was tested and worked for me. I put RD and SPC in the
rows where there were subtotals in the data. They were excluded from the
calculations and gave the same results as worksheet functions which were
applied to the appropriate subsets of the data.

Formula worked both in code and using the formula it produces in a
worksheet. So no, I wouldn't think it needs to be changed except for
changing the RD/SPC to match the strings in column A. Possibly those cells
contain "RD " or " RD" for example which of course do not equal "RD" and
those rows would not be excluded.

 

[Guest]

You were right, Tom. I think it's one of those things where I was staring at
it for so long that I wasn't thinking straight. I hadn't trimmed column A.
Once I did that, it worked perfectly. Thanks again for your help.

Phil