average and StdDev on dynamic range

I Hate Computers

New Member
Joined
Dec 4, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.


1607112315005.png
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
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
 

I Hate Computers

New Member
Joined
Dec 4, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 4, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

worked! tnx
 

I Hate Computers

New Member
Joined
Dec 4, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

I Hate Computers

New Member
Joined
Dec 4, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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)
1607442423012.png
 

I Hate Computers

New Member
Joined
Dec 4, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

1607442598831.png
 

Watch MrExcel Video

Forum statistics

Threads
1,130,193
Messages
5,640,771
Members
417,166
Latest member
Funwayo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top