# average and StdDev on dynamic range

#### I Hate Computers

##### New Member
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. ### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### offthelip

##### Well-known Member
try this code that puts the formula in the cells for you:
VBA Code:
``````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``````

• jewkes6000

#### I Hate Computers

##### New Member
try this code that puts the formula in the cells for you:
VBA Code:
``````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

##### Well-known Member
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

• jewkes6000

worked! tnx

#### I Hate Computers

##### New Member
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

##### Well-known Member

try this:
VBA Code:
``````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``````

• jewkes6000

#### I Hate Computers

##### New Member
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

##### New Member
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

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

Replies
4
Views
66
Replies
4
Views
273
Replies
4
Views
78
Replies
0
Views
56
Replies
5
Views
462

### Forum statistics

1,129,331
Messages
5,635,655
Members
416,870
Latest member
rikimon2 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back