Formula to figure average efficiency based on criteria

  • Thread starter Thread starter scaldwell
  • Start date Start date
Hi
not looking at your example but if you have three columns (A,B,C) and
want to calculate the average for column C based on the conditions for
col. A and col. B use the following array formula (entered with
CTRL+SHIFT+ENTER):
=AVERAGE(IF((A1:A100="cond_1")*(B1:B100="cond_2"),C1:C100))
 
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!
 
Back
Top