PDA

View Full Version : AverageIF function



Klartigue
07-28-2014, 09:25 AM
Hello,

I am trying to write a code that calculates the average price based on specific criteria. The lines will always change so it needs to be a general code that says for all the same account in column A, if the security in column C is the same, then average the prices listed in column K and place that number in column O.

Attached is a example of the sheet I am working with and how I want to average the price.

Bob Phillips
07-28-2014, 10:48 AM
In O6, use

=AVERAGEIFS($K$1:$K$28,$A$1:$A$28,$A6,$B$1:$B$28,$B6,$C$1:$C$28,$C6)

Klartigue
07-28-2014, 11:07 AM
That works, I placed the formula in this code and it seems to be generic enough to work with all different output each time.


Sub TestAverageif()
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
With .Cells(i, "O").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS(R[-5]C[-4]:R[21]C[-4],R[-5]C[-14]:R[21]C[-14],R[-1]C[-14],R[-5]C[-13]:R[21]C[-13],R[-1]C[-13],R[-5]C[-12]:R[21]C[-12],R[-1]C[-12])"

End With

Next i
End With
End Sub

Klartigue
07-28-2014, 12:15 PM
Actually this doesn't seem to be working. In cell O1 the formula is =AVERAGEIFS(K23:K1048573,A23:A1048573,A1,B23:B1048573,B1,C23:C1048573,C1). It should always averaging K2 down to lastrow in K if the values in A are the same and the values in C are the same. This keeps shifting down starting at K23 which is not correct

Klartigue
07-28-2014, 12:26 PM
I think something like this will work:


Sub TestAverageif()
Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
With .Cells(i, "O").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS(R2C11:R1048576C11,R2C1:R1048576C1,R2C1:R1048576C1,R2C3:R1048576 C3,R2C3:R1048576C3)"

End With

Next i
End With
End Sub

Bob Phillips
07-28-2014, 03:18 PM
Just use


Sub TestAverageif()
Dim Lastrow As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").Resize(Lastrow-1).Formula = "=AVERAGEIFS($K$2:$K$" & Lastrow & ",$A$2:$A$" & Lastrow & ",$A2,$B$2:$B$" & Lastrow & ",$B2,$C$2:$C$" & Lastrow & ",$C6)"
End With
End Sub