Formula to figure average efficiency based on criteria

[scaldwell]

Need help with an IF-THEN formula to calculate an avg. efficiency base
on 2 different columns. I have created a sample of what I need hel
with and attached it as a file.

Thank

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51420

 

[Frank Kabel]

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))

 

[kkknie]

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!