PDA

View Full Version : Solved: Getting average of a range within a loop



Mavis Beacon
03-07-2013, 12:38 AM
Hello,
I have excel 2007. I'm trying to get the average of a column of numbers. I can't use exact cell addresses as I've used a loop that does this for every column in a worksheet. There are a different number of rows in every column and a different number of columns in every worksheet. The line that I'm having trouble with is

ActiveCell.FormulaArray = [Average(Range(Cells(ActiveCell.Offset(-40, 0).End(xlDown).Row, ActiveCell.Column)))]

When I run it, I keep getting the error message #VALUE!.

What I'm trying to get the code to do is to 1) loop through columns of data in a worksheet, 2) insert a blank column next to each one, 3) copy over data from the previous column if the value of the cell is <2, 4) get the mean and STDEV of the new column.

Could anyone tell me where I'm going wrong?
Many Thanks.


My full code is:

Sub TEST()
'
' TEST Macro
'
' Keyboard Shortcut: Ctrl+o
'
Cells(3, 1).Activate
Dim Lastcol As Integer, c As Integer
Lastcol = ActiveSheet.UsedRange.Columns.Count

For c = 1 To Lastcol

If Cells(3, c) <> vbNullString Then
Dim StartPoint As Range
Set StartPoint = ActiveCell
StartPoint.Offset(0, 1).EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
StartPoint.Offset(0, 1).Select
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>2,"""",RC[-1])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -1) = vbNullString
StartPoint.Offset(0, 1).EntireColumn.Select
Selection.NumberFormat = "0.0000"
StartPoint.Offset(40, 1).Select
ActiveCell.FormulaArray = [Average(Range(Cells(ActiveCell.Offset(-40, 0).End(xlDown).Row, ActiveCell.Column)))]
StartPoint.Offset(0, 2).Select

End If
Next c
End Sub

Bob Phillips
03-07-2013, 02:01 AM
Sub TEST()
Dim Lastcol As Long, lastrow As Long
Dim i As Long, ii As Long
Dim StartPoint As Range

With ActiveSheet

Lastcol = .UsedRange.Columns.Count

For i = Lastcol To 1 Step -1

If .Cells(3, i) <> vbNullString Then

lastrow = .Cells(.Rows.Count, i).End(xlUp).Row
.Columns(i + 1).Insert
.Cells(3, i + 1).Resize(lastrow - 2).FormulaR1C1 = "=IF(RC[-1]>2,"""",RC[-1])"
.Columns(i + 1).NumberFormat = "0.0000"
.Cells(43, i + 1).FormulaArray = "=Average(" & .Range(.Cells(3, i + 1), .Cells(43, i + 1)).Address & ")"
End If
Next i
End With
End Sub

Mavis Beacon
03-07-2013, 03:12 AM
Thanks so much! Reply much appreciated. It runs through the file now but for some reason when it gives the average, it gives 0.000? I want to get the average of just the cells that contain numbers. When I click on the cell that has the 0.000, it reads: =AVERAGE($C$3:$C$43) in the formula bar, so it's averaging all the values but still giving 0.000? I tried removing the number format line in case that was the problem but that didn't work. Is it something in my workbook? Did the code run on yours? Many thanks!
M.

P.S. could you tell me, or direct me somewhere that explains the syntax in this line of code?
.Cells(43, i + 1).FormulaArray = "=Average(" & .Range(.Cells(3, i + 1), .Cells(43, i + 1)).Address & ")"

I think I understand .Cells(43, i + 1).FormulaArray = is to put the formula in that particular cell and that "=Average( is to get the average of the following cells, but I don't understand the " &.Range part? What do the "" and the & do? Any help would be appreciated!

Mavis Beacon
03-07-2013, 03:17 AM
Sorry, also forgot to ask, rather than have it calculate from row 3 to row 43, how can I get it to just calculate the rows with values in them as each column has a different number of rows in them. Thanks again!

Mavis Beacon
03-07-2013, 03:30 AM
Used this and it worked perfectly:
.Cells(43, i + 1).FormulaArray = "=Average(" & .Range(.Cells(3, i + 1), .Cells(lastrow, i + 1)).Address & ")"

Thanks so much for your help!

Bob Phillips
03-07-2013, 03:38 AM
P.S. could you tell me, or direct me somewhere that explains the syntax in this line of code?
.Cells(43, i + 1).FormulaArray = "=Average(" & .Range(.Cells(3, i + 1), .Cells(43, i + 1)).Address & ")"

I think I understand .Cells(43, i + 1).FormulaArray = is to put the formula in that particular cell and that "=Average( is to get the average of the following cells, but I don't understand the " &.Range part? What do the "" and the & do? Any help would be appreciated!

It is building the formula string. The =AVERAGE( is just text, it then needs to concatenate (&) that with the address of the range to average (address as VBA works on range objects, Excel works on cell references such as B3:B10, .Address gets this address from the range), and finally concatenate a closing bracket.

snb
03-07-2013, 03:49 AM
Sub M_snb()
y = Application.Average(Columns(2))
End Sub

Mavis Beacon
03-07-2013, 04:14 AM
Thank a mill! I still have one small problem. When I use the lastrow, it gets the average and stdev of cells from row3 to row 37 now, but I want it just to calculate the cells that contain values (I checked this using STDEV and it returns a different number when done manually). Is there anything I could use instead of lastrow?

Mavis Beacon
03-07-2013, 04:22 AM
Sorry!! That's me being an idiot, I just checked it against the mean and stdev done out manually and they're the same.. Thanks so much for your help!

Bob Phillips
03-07-2013, 04:26 AM
This version doesn't insert columns

Sub TEST()
Dim Lastcol As Long, Lastrow As Long
Dim i As Long

With ActiveSheet

Lastcol = .UsedRange.Columns.Count

For i = Lastcol To 1 Step -1

If .Cells(3, i) <> vbNullString Then

Lastrow = .Cells(.Rows.Count, i).End(xlUp).Row
.Cells(43, i).FormulaArray = "=AVERAGE(IF(R3C:R" & Lastrow & "C<=2,R3C:R" & Lastrow & "C))"
.Cells(43, i).NumberFormat = "0.0000"
End If
Next i
End With
End Sub

snb
03-07-2013, 05:24 AM
or

Sub M_snb()
For Each cl In UsedRange.Columns
Cells(43, cl.Column).Resize(2) = Application.Transpose(Array(Application.Average(Cells(1, cl.Column).Resize(42)), Application.StDev(Cells(1, cl.Column).Resize(42))))
Next
End Sub

Mavis Beacon
03-12-2013, 10:50 AM
Thanks a mill!!