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)

 

[joeu2004]

PS....

If namcol(i) = "Mon" Then monsum = monsum + (fitcol(i) - monav) ^ 2: n = n + 1

And I wrote previously: If LCase(namcol(i)) = "mon" Then monsum = monsum + fitcol(i) ^ 2: n = n + 1

My posting #6 demonstrates the correctness of the mathematically-equivalent alternative.

But your use of (fitcol(i) - monav)^2 in the summation loop and Sqr(monsum / n) should calculate the conditional std dev correctly -- for STDEVP.

However, if you want the conditional STDEV, the Sqr expression should be Sqr(monsum / (n-1)).

-----

Set fitcol = taborg.Offset(, 4 + reps).Resize(brtt)
Set namcol = taborg.Offset(-1).Resize(brtt + 1)

My use of namcol(i) and fitcol(i) assumes two parallel ranges, each row associating line-by-line.

That was readily apparent from your original posting, to wit:

Set namcol = Range("A2",Range("A2").End(xlDown))
Set fitcol = Range("D2",Range("D2").End(xlDown))

It is not so apparent to me with your current assignments above.

So I cannot say with impunity that my VBA if-statement above still works as intended, only because of the "i" indexing of namcol and fitcol.

The correct range references are up to you to determine.

In your posting #7, I do not see any Excel formulas or VBA code that indicate the intended std dev calculation for that particular data.

 

[joeu2004]

Final PS (I promise)....

My use of namcol(i) and fitcol(i) assumes two parallel ranges, each row associating line-by-line. That was readily apparent from your original posting [....] It is not so apparent to me with your current assignments above.

Okay, that much is apparent, on closer inspection.

It is difficult for me to execute your VBA code because it references "reps" and slorg, which are undefined in your posting.

I can work around that.

However, with some hacks, it is apparent that "namcol" and "fitcol" are not what I expected -- because brtt is 1, not 16 as I would expect.

I won't debug your VBA code.

 

[joeu2004]

Errata (not a PS -- wink)....

Set fitcol = taborg.Offset(, 4 + reps).Resize(brtt)
Set namcol = taborg.Offset(-1).Resize(brtt + 1)

My use of namcol(i) and fitcol(i) assumes two parallel ranges, each row associating line-by-line. [....] It is not so apparent to me with your current assignments above.

Okay, that much is apparent, on closer inspection.

No, I was right the first time: effectively taborg.Offset(0) vs. taborg.Offset(-1). Klunk!

Okay, that is one of your mistakes.

But to reiterate, the bigger mistake is: brtt is 1. Thus, namcol and fitcol are not the entirety of their respective columns.

The reason is "obvious": taborg.End(xlDown) is a single cell, not a range. So taborg.End(xlDown).Count is 1.

Enough said! Good luck!

 

[ThomasOES]

Set taborg = Range("A2")
Set namcol = Range(taborg, taborg.End(xlDown))
brtt = namcol.Count
reps = 4 'Range("reps").Value in this case reps = 4
Set fitcol = taborg.Offset(, 4 + reps).Resize(brtt)

sorry about that Joe
this should make the two columns parallel

 

[ThomasOES]

Set taborg = Range("A2")
Set namcol = Range(taborg, taborg.End(xlDown))
brtt = namcol.Count
reps = 4 'Range("reps").Value in this case reps = 4
Set fitcol = taborg.Offset(, 4 + reps).Resize(brtt)

For i = 1 To brtt
    If namcol(i) = "Mon" Then monsum = monsum + (fitcol(i) - monav) ^ 2: n = n + 1
Next
samstdev = Sqr(monsum / (n - 1))

works great now.
Thanks for the idea of assembling the sum of the squared differences then manually calculating the sample standard deviation
I'll use this method for many other instances

Regards
Tom