Since I'm not so swift when it comes to array formulas, I developed
quick bit of code you can run after you have entered new data. Jus
put it in a module and run it from a button.
Code
-------------------
Sub DoAvg()
Dim i As Long
Dim iRowLast As Long
Dim iRowFirst As Long
Dim rNum As Double
Dim rDen As Double
Dim strLast As String
strLast = "Nothing"
rNum = 0
rDen = 0
iRowFirst = Range("L65536").End(xlUp).Row + 1
iRowLast = Range("A65536").End(xlUp).Row + 1
For i = iRowFirst To iRowLast
If strLast < Cells(i, 1).Value & Cells(i, 3).Value Then
If strLast < "Nothing" Then
If rDen < 0 Then
Cells(i - 1, 12).Value = rNum / rDen * 100
Else
Cells(i - 1, 12).Value = "Error"
End If
End If
rNum = 0
rDen = 0
End If
rNum = rNum + Cells(i, 9) * Cells(i, 11)
rDen = rDen + Cells(i, 9)
strLast = Cells(i, 1).Value & Cells(i, 3).Value
Next
End Su
-------------------
I tested it on your workbook and it worked fine. I did multiply th
average by 100 to get percent. Take this out if you format that colum
as %.
But, if Frank's suggestion works for you, by all means use it!
--
Message posted from
http://www.ExcelForum.com