average and StdDev on dynamic range

[I Hate Computers]

I need to calc the avg and stdev on a vertical col of numbers. anywhere from 5 to 50 numbers. I have all the other macros worked out to add rows and such between groups and to move around the active cell. I'm stuck with getting the average and stdev on a range of numbers that isn't always the same. length.

What I need is a macro to calc the average of C192 to C203 and put it into C204. and to calc the Stdev of C192 to C203 and put it into C205. on a relative basis. maybe 20 cells down there will be another set of data that needs the same calcs. I don't mind pressing cntrl-a 200 times to do this. and yes, I tried using Sendkeys - didn't work. don't think less of me for trying SendKeys.

 

[offthelip]

try this code that puts the formula in the cells for you:

Sub test()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
strow = 1
For i = 1 To lastrow
 If inarr(i, 1) = "avg" Then
   Cells(i, 3).Formula = "=Average(C" & strow & ":C" & i - 1 & ")"
 End If
 If inarr(i, 1) = "stdev" Then
   Cells(i, 3).Formula = "=stdev(C" & strow & ":C" & i - 2 & ")"
   strow = i + 1
 End If
Next i
End Sub

 

[I Hate Computers]

try this code that puts the formula in the cells for you:

Sub test()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
strow = 1
For i = 1 To lastrow
If inarr(i, 1) = "avg" Then
   Cells(i, 3).Formula = "=Average(C" & strow & ":C" & i - 1 & ")"
End If
If inarr(i, 1) = "stdev" Then
   Cells(i, 3).Formula = "=stdev(C" & strow & ":C" & i - 2 & ")"
   strow = i + 1
End If
Next i
End Sub

what cell should be active when I run the macro?

 

[offthelip]

It doesn't matter which cell is active, the code looks at column B and put the "average" equation in column C on every row where the txt "avg" is detected
Similarly it puts the "Stddev" equation everywhere it detects stdev in column B

 

[I Hate Computers]

worked! tnx

 

[I Hate Computers]

OK - I want to add the count value below stdev. I burned 2 days trying to do this myself - and failed. I can get the formula, but the range is wrong. ... can I trouble you to add count?

 

[offthelip]

try this:

Sub test()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
strow = 1
For i = 1 To lastrow
 If inarr(i, 1) = "avg" Then
   Cells(i, 3).Formula = "=Average(C" & strow & ":C" & i - 1 & ")"
 End If
 If inarr(i, 1) = "stdev" Then
   Cells(i, 3).Formula = "=stdev(C" & strow & ":C" & i - 2 & ")"
 End If
 If inarr(i, 1) = "count" Then
   Cells(i, 3).Formula = "=count(C" & strow & ":C" & i - 3 & ")"
   strow = i + 1
 End If
Next i
End Sub

 

[I Hate Computers]

I don't feel so bad now. I tried what you have above - avg and stdev are working correctly. count is not finding the correct range (see below)

 

[I Hate Computers]

more info - a previous macro adds 4 blank rows when the cavity changes - and adds the words avg, stdev, count. so there is 1 blank row below count

 

[I Hate Computers]

never mind. I failed to rem out a strow=i+1 line. it works.