standard deviation for certain cells in a range

[ThomasOES]

This works for the average. Yet I need the standard deviation

Set namcol = Range("A2",Range("A2").End(xlDown))
Set fitcol = Range("D2",Range("D2").End(xlDown))
monav = WorksheetFunction.AverageIf(namcol, "Mon", fitcol)

 

[ThomasOES]

This works but I cant use a formula. The ranges need to be dynamic

Selection.FormulaArray = "=STDEV(IF(A1:A17=""Mon"",I1:I17))"

 

[joeu2004]

Choose your poison.

Method 1:
sd = Evaluate("stdev(if(" & namcol.Address & "= ""Mon"", " & fitcol.Address & "))")

Method 2:

monsum = 0: n = 0
For i = 1 To namcol.Count
   If LCase(namcol(i)) = "mon" Then monsum = monsum + fitcol(i) ^ 2: n = n + 1
Next
sd = Sqr((monsum - n * monav ^ 2) / (n - 1))

Method 1 really does its work in the Excel thread. It is not a "pure" VBA solution.

Aside.... Consider whether to use STDEVP (exact std dev). STDEV is an estimated std dev, assuming that fitcol is sample of a larger "population".

If you decide to use STDEVP, the calculation for Method 2 is:

sd = Sqr(monsum/n - monav ^ 2)

 

[joeu2004]

Notes (too late to edit)....

sd = Evaluate("stdev(if(" & namcol.Address & "= ""Mon"", " & fitcol.Address & "))")
[....]
sd = Sqr((monsum - n * monav ^ 2) / (n - 1))

There can be an infinitesimal difference between the two calculations, due to binary arithmetic anomalies.

Although "infinitesimal", the difference might be visible when displaying 15 significant digits.

-----

If LCase(namcol(i)) = "mon" Then

In Excel, A2="Mon" and A2="mon" are both true, no matter how "mon" is capitalized in A2 (even "mOn").

But VBA requires an exact match. It is equivalent to EXACT(A2, "Mon") in Excel.

The use of LCase causes the VBA comparison to be compatible with the Excel comparison. But it might not be necessary for your data.

 

[ThomasOES]

=STDEV(I2,I3,I4,I14)

When I manually enter the cells to get standard deviation my result is 0.000275
cell values are 0.0216, 0.0211, 0.0217, 0.0213
Your code value is 0.00149

 

[joeu2004]

When I manually enter the cells to get standard deviation my result is 0.000275 [....] Your code value is 0.00149

I cannot comment on any mistakes you might have made, due to insufficient information.

After reviewing the following, if you are still unable to make the code work, post the Excel formulas and data and the VBA code and result in the same manner as below, and I will see if I can spot the problem

-----

The following is the Excel data and formulas that I used to validate my suggestions.

vba stdevif.xlsm
	A			D	E	F	G	H
1						formula		check
2	Fri			70.55		29.0578226300596	STDEVP	29.0578226300596
3	Mon			12.18		32.4876333394725	STDEV	32.4876333394725
4	Wed			94.14
5	Fri			92.04
6	mon			59.32
7	Wed			37.06
8	Fri			37.21
9	Mon			62.50
10	Wed			45.64
11	Fri			39.54
12	Mon			79.26
13	Wed			31.13
14	Fri			78.11
15	Mon			99.90
16	Wed			44.60
Sheet1

Formulas:
F2: { STDEVP(IF($A$2:$A$16="mon",$D$2:$D$16)) }
F3: { STDEV(IF($A$2:$A$16="mon",$D$2:$D$16)) }
H2: =STDEVP($D$3,$D$6,$D$9,$D$12,$D$15)
H3: =STDEV($D$3,$D$6,$D$9,$D$12,$D$15)
Caveat: We must manually array-enter formulas in F2 and F3 (sigh). Do not type the curly braces. Press ctrl+shift+Enter instead of just Enter.
[/CODE]

The following is the VBA code.

' it is prudent to declare all variables.
' I omit declarations for simplicity
Sub doit()
Set namcol = Range("A2", Range("A2").End(xlDown))
Set fitcol = Range("D2", Range("D2").End(xlDown))
monav = WorksheetFunction.AverageIf(namcol, "Mon", fitcol)
monsum = 0: n = 0
For i = 1 To namcol.Count
    If LCase(namcol(i)) = "mon" Then monsum = monsum + fitcol(i) ^ 2: n = n + 1
Next
sdP = Sqr(monsum / n - monav ^ 2)               ' compare with STDEVP
sdS = Sqr((monsum - n * monav ^ 2) / (n - 1))   ' compare with STDEV
xlsdS = Evaluate("stdev(if(" & namcol.Address & "= ""Mon"", " & fitcol.Address & "))")
MsgBox "STDEVP" & vbNewLine & _
    sdP & vbNewLine & Range("f2") & vbNewLine & sdP - Range("f2") & _
    vbNewLine & vbNewLine & "STDEV" & _
    vbNewLine & sdS & vbNewLine & Range("f3") & vbNewLine & sdS - Range("f3") & _
    vbNewLine & vbNewLine & "STDEV (EVALUATE)" & _
    vbNewLine & xlsdS & vbNewLine & Range("f3") & vbNewLine & xlsdS - Range("f3")
End Sub

The following is the result.

 

[ThomasOES]

PTP TEST GDS.xlsm
	A	B	C	D	E	F	G	H	I
1	Sample	Pos	Al	D-1	D-2	D-3	D-4	Avg	Fit
2	Mon	-1	-3	0.0225	0.0225	0.0225	0.0225	0.0225	0.0216
3	Mon	-1	-3	0.0219	0.0219	0.0219	0.0219	0.0219	0.0211
4	Mon	-1	-3	0.0226	0.0226	0.0226	0.0226	0.0226	0.0217
5	1Q21L1	11	-2	0.0203	0.0203	0.0205	0.0203	0.0204	0.0196
6	1Q21L2	12	-2	0.0364	0.0364	0.0366	0.0364	0.0365	0.0352
7	E 195-1D	10	-0.024	0.02	0.0201	0.0198	0.0201	0.02	0.0192
8	12X 61500	1	(0.0066)	0.0042	0.0037	0.0042	0.0041	0.0041	0.0038
9	CKD 165I	9	0.01	0.0095	0.0096	0.0095	0.0095	0.0095	0.0091
10	BSP 2022	8	0.012	0.013	0.0132	0.0131	0.0129	0.0131	0.0125
11	BS 8620A	5	0.016	0.0166	0.0166	0.0165	0.017	0.0167	0.016
12	BS 1951	2	0.02	0.0221	0.0221	0.0221	0.0226	0.0222	0.0213
13	BS 8620B	6	0.022	0.0239	0.0239	0.0237	0.0235	0.0238	0.0229
14	Mon	-1	0.0223	0.0222	0.0222	0.0222	0.0222	0.0222	0.0213
15	BS 61D	3	0.023	0.0241	0.0241	0.024	0.0241	0.0241	0.0232
16	BS 67B	4	0.024	0.0243	0.0244	0.0244	0.0242	0.0243	0.0234
17	BS 8740	7	0.037	0.038	0.038	0.038	0.0377	0.0379	0.0365
Al

Set taborg = Range("A2")
brtt = taborg.End(xlDown).Count
reps = Range("reps").Value
Set fitcol = taborg.Offset(, 4 + reps).Resize(brtt)
Set namcol = taborg.Offset(-1).Resize(brtt + 1)
monsum = 0: n = 0
For i = 1 To namcol.Count
    If namcol(i) = "Mon" Then monsum = monsum + (fitcol(i) - monav) ^ 2: n = n + 1
Next
slorg.Offset(5, 12).Value = Sqr(monsum / n)

 

[ThomasOES]

Mon is an abbreviation of monitor. After the data is fitted I need some info of the monitor performance. Avg, StDev, ect. Needs to be dynamic. Sometimes theres many more or less samples.

 

[ThomasOES]

monav = WorksheetFunction.AverageIf(namcol, "Mon", fitcol)

Forgot to include the monav

 

[joeu2004]

slorg.Offset(5, 12).Value = Sqr(monsum / n)

I wrote previously: Sqr(monsum / n - monav ^ 2)

I should have explained that this is an mathematically-equivalent alternative to the typical std dev expression.

However, that expression calculates STDEVP, not STDEV.

As I wrote previously, the expression for STDEV is: Sqr((monsum - n * monav ^ 2) / (n - 1))

I expect you to copy-and-paste from the text of my postings, then edit as needed (to change variable names). That minimizes your mistakes.

-----

Minor typo....

F2: { STDEVP(IF($A$2:$A$16="mon",$D$2:$D$16)) }
F3: { STDEV(IF($A$2:$A$16="mon",$D$2:$D$16)) }

Of course, there is an "=" before "STDEV" in each formula, which you can see by selecting or hovering the cursor over each cell in the XL2BB snippet. So:

F2: { =STDEVP(IF($A$2:$A$16="mon",$D$2:$D$16)) }
F3: { =STDEV(IF($A$2:$A$16="mon",$D$2:$D$16)) }